Database Reference
In-Depth Information
Dynamic
SQL is a term for SQL that isn't finalized at design time, but rather is as-
sembled at runtime by any number of dynamic criteria. Dynamic SQL is used when the
exact requirements of a SQL statement aren't known until runtime, or when the SQL
needs to change while the application is running. Dynamic SQL lets you modify
column lists,
where
clauses, joins, and any other portion of an SQL statement while
an application is running.
APEX supports dynamic SQL in reports and can support PL/SQL functions return-
ing SQL statements as a result. There are some constraints, however. Functions must
return a valid SQL statement. Depending on the implementation, a statement may need
to return a set of generic columns if the number of columns isn't known or will vary.
The Help Desk application has the requirement to differentiate public tickets from
private ones. To accomplish that goal, you can implement a Public Flag feature. Imple-
menting the flag requires a quick update to your data model and then an implementa-
tion of dynamic SQL on the Home Page report. Start by making the data modification:
1.
Navigate to the
SQL Workshop
.
2.
Click the
SQL Commands
icon.
3.
Enter the following SQL statement in the text area, and click the
Run
but-
ton. This adds the new column called
PUBLIC_FLAG
to the
TICKETS
table:
ALTER TABLE tickets ADD (public_flag
VARCHAR2(1))
4.
Enter the following SQL statement in the text area, replacing the current
statement, and click
Run
. This updates all the current tickets to a default
value of
N
:
UPDATE tickets SET public_flag = 'N'
Now that the data-model modifications are complete, you can move on
to the application. Add the option to see and edit the new value in the tick-
et edit screen:
5.
Click the
Application Builder
tab.
6.
Select the
Help Desk
application by clicking the corresponding icon or
link.
7.
Edit
Page 210
of the application.
