Databases Reference
In-Depth Information
This code implements logic that makes a decision based on the user substitution variable
:APP_USER
and tailors
the
htp.p
output according to that distinguishing factor. APEX provides “nobody” as a username when a user isn't yet
logged in, so the logic keys off of that value.
When the PL/SQL region is generated for a user who isn't yet logged in, a simple welcome message is produced
(see Figure
8-36
). When a user who has credentials is logged in to the application, a message similar to that in
Figure
8-37
is produced that shows a user-specific greeting and a quick count of the number of open tickets assigned
to that user.
Figure 8-36.
Issue Tracker PL/SQL region when the user isn't yet logged in
Figure 8-37.
With an authenticated user, the PL/SQL region generates a greeting and a ticket count
In this section, you've created a dynamic PL/SQL region that alters the output based on the application user. This
section's example, although simple, shows how the content of a region can be as dynamic as necessary with the use of
PL/SQL in the database.
Dynamic SQL
Dynamic
SQL is a term for SQL that isn't finalized at design time, but rather is assembled 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 returning 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. Implementing the flag requires a quick update to your data model
and then an implementation 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
button. This adds the
new column called
PUBLIC_FLAG
to the
TICKETS
table:
ALTER TABLE tickets ADD (public_flag VARCHAR2(1))
