3 Application Builder Concepts

Current Page in Expression 1

Current page in Expression 1 evaluates to true if the current page number is contained within the comma-delimited list of pages in Expression 1. For example:

3,100,203

If the current page is 100, then this condition evaluates to true and the condition passes.

Exists

Exists (SQL query returns at least one row) is expressed as a SQL query. If the query returns at least one row, then the condition evaluates as true. For example:

SELECT 1 FROM employees WHERE department_id = :P101_DEPTNO

This example references item P101_DEPTNO as a bind variable. You can use bind variables within application processes and SQL query regions to reference item session state. If one or more employees are in the department identified by the value of P101_DEPTNO, then the condition evaluates as true.

PL/SQL Expression

Use PL/SQL Expression to specify an expression in valid PL/SQL syntax that evaluates to true or false. For example:

NVL(:MY_ITEM,'NO') = 'YES'

If the value of :MY_ITEM is Yes, as in the previous example, then the condition evaluates as true. If the value of :MY_ITEM is No, then the condition evaluates as false.

About the Session State Page

The Session State page provides valuable information about the session in which the application is currently running. To locate a specific page, enter the page number in the page field and click Go. Table 3-1 describes the various types of information available on the Session State page.

About APEX_UTIL.PREPARE_URL

Oracle provides a utility function to enable developers to use PL/SQL to generate anchors to application pages using the f?p syntax. Use APEX_UTIL.PREPARE_URL whenever PL/SQL in the application produces f?p anchors that might require a zero session ID. To learn more, see "PREPARE_URL" in Oracle Application Express API Reference.

How the Zero Session ID Feature Works

The zero session ID feature provides support for pages having anchors in the HTML (such as in templates or HTML regions). When the Application Express engine encounters f?p anchors it recognizes them and replaces the session ID with zero, if certain criteria are met.

The Application Express engine replaces the session ID component with zero when it detects one of the following patterns:

• &APP_ID.

• The numeric ID of the current application

• One of the following patterns:

  • the all-uppercase or all-lowercase alphanumeric alias of the current application followed by a colon followed by the numeric ID of a public page in the current application

  • the all-uppercase or all-lowercase alphanumeric alias of a public page in the current application followed by a colon followed by &APP_SESSION. or &SESSION.

Examples of patterns in HTML that are candidates for zero-session ID replacement include:

• Page 10 is a public page:

  "f?p=&APP_ID.:10:&APP_SESSION. 
  

• Page 10 is a public page:

  "f?p=100:10:&APP_SESSION.
  

• home is an alias for a public page:

  "f?p=&APP_ID.:home:&APP_SESSION. 
  

• HOME is an alias for a public page:

  "f?p=&APP_ID.:HOME:&APP_SESSION.
  

• PEOPLE is an application alias and HOME is an alias for a public page:

  "f?p=PEOPLE:HOME:&APP_SESSION
  

In these examples, the pattern &APP_SESSION. (or &SESSION., not shown) is replaced with zero if the application is operating in zero-session ID mode and the user is a public user. Note that the user is considered a public user if the following PL/SQL expression is true:

:app_user in ('nobody','HTMLDB_PUBLIC_USER','PUBLIC_USER','ANONYMOUS','APEX_PUBLIC_USER')" 

Form Submission Example

When a user submits a page, the Application Express engine automatically stores values typed into fields (items) in session state. For example, suppose you have an application containing two pages. The first page of the application contains a form in which a user can enter a phone number. You defined this form by creating an item named P2_PhoneNo. On the second page, you want to display the information the user enters in the form.

When the page is submitted, Oracle Application Express captures the value entered in the phone number field and stores the value for future use. The phone number entered by the user can then be retrieved from session state by referencing the item associated with the field on the page.

Clearing Cache by Item

Clearing cache for a single item resets the value of the item to null. For example, you might use this approach to make sure a specific item's value is null when a page is prepared for rendering.

The following example uses standard f?p syntax to clear the cache for an item. This example calls page 5 of application 100. Placing MY_ITEM in the ClearCache position of the f?p syntax resets the value of MY_ITEM to NULL.

f?p=100:5:&APP_SESSION.::NO:MY_ITEM

The following example resets the value of the items THE_EMPNO and THE_DEPTNO.

f?p=100:5:&APP_SESSION.::NO:THE_EMPNO,THE_DEPTNO

Clearing Cache by Page

Caching application items provides an effective way to maintain session state. However, there are occasions when you may want to clear the cache for all items on a page. For example, suppose you needed to clear all fields on a page when a user clicks a link that creates a new order. By clearing the cache for an entire page, you set the value of all items on the page to null.

f?p=6000:6003:&APP_SESSION.::NO:RP,6004,6014 

f?p=6000:6003:&APP_SESSION.::NO:6003:MY_ITEM:1234 

f?p=6000:6004:&APP_SESSION.::NO:6003:MY_ITEM1,MY_ITEM2,MY_ITEM3:1234,,5678 

Clearing Cache for an Entire Application

You can clear an application's cache by using f?p syntax and creating a Clear Cache argument using the keyword APP. For example:

f?p=App:Page:Session::NO:APP

Clearing Cache for the Current User Session

Another approach to clearing an application's cache is to create a Clear Cache argument using the keyword SESSION. For example:

f?p=6000:6004:12507785108488427528::NO:SESSION

Using Bind Variables in Regions Based on a SQL Query or LOV

If your region type is defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV), you can reference session state using the following syntax:

:MY_ITEM

One common way to do this is to incorporate a session state variable in a WHERE clause. The following example shows how to bind the value of the item THE_DEPTNO into a region defined from a SQL Query.

SELECT last_name, job_id, salary
FROM employees
WHERE department_id = :THE_DEPTNO

Using Bind Variables in PL/SQL Procedures

For region types defined as a PL/SQL Procedure, regions are constructed using PL/SQL anonymous block syntax. In other words, the beginning and ending keywords are used to enclose the PL/SQL block. For example:

IF :P1_JOB IS NOT NULL THEN
  INSERT INTO employees (employee_id, first_name, job_id) 
  VALUES (:P1_EMP_ID, :P1_NAME, :P1_JOB)
end if;

In this example, the values of the employee_id, first_name, and job_id are populated by the values of P1_EMP_ID, P1_NAME, and P1_JOB.

Determining Substitution String Usage within a Given Template

You can determine what template-specific substitution strings are supported in which templates by viewing the template definition. See "Customizing Templates".

APP_ALIAS

APP_ALIAS is an alphanumeric name for the current application. APP_ALIAS is different from the APP_ID in that the APP_ID must be unique over all workspaces and all applications hosted in one database. In contrast, APP_ALIAS must be unique within a workspace. For example, by using the same APP_ALIAS you can create the application, ABC, in two different workspaces. You can use APP_ALIAS almost anywhere APP_ID can be used. For example, f?p syntax can use an APP_ALIAS or an application ID as demonstrated in this example:

f?p=ABC:1:&APP_SESSION.

This example runs application ABC, page 1 using the current session.

Table 3-4 describes the supported syntax for referencing APP_ALIAS.

The following is an HTML example:

Click me to go to page 1 <a href="f?p=&APP_ALIAS.:1:&APP_SESSION."> of the current application</a>

APP_ID

APP_ID identifies the application ID of the currently executing application. Table 3-5 describes the supported syntax for referencing APP_ID.

The following is an example of a substitution string reference:

f?p=&APP_ID.:40:&APP_SESSION.

APP_IMAGES

Use this substitution string to reference uploaded images, JavaScript, and cascading style sheets that are specific to a given application and are not shared over many applications. If you upload a file and make it specific to an application, then you must use this substitution string, or bind variable. Table 3-6 describes the supported syntax for referencing APP_IMAGES.

APP_PAGE_ID

APP_PAGE_ID is the current application ID. For example, if your application was on page 3, then the result would be 3. Using this syntax is useful when writing application components that need to work generically in multiple applications. Table 3-7 describes the supported syntax for referencing APP_PAGE_ID.

The following is an example of a substitution string reference:

f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.

APP_SESSION

APP_SESSION is one of the most commonly used built-in substitution strings. You can use this substitution string to create hypertext links between application pages that maintain a session state by passing the session number. Note that you can also use the substitution string SESSION in place of APP_SESSION. Table 3-8 describes the supported syntax for referencing APP_SESSION.

Consider the following examples:

• From within an HTML region:

  <a href="f?p=100:5:&APP_SESSION.">click me</a> 
  

• Using PL/SQL:

  htf.anchor('f?p=100:5:'||V('APP_SESSION'),'click me');
  

• Using a SQL query:

  SELECT htf.anchor('f?p=100:5:'||:APP_SESSION,'clickme') FROM DUAL;
  

APP_UNIQUE_PAGE_ID

APP_UNIQUE_PAGE_ID is an integer generated from an Oracle sequence which is unique for each page view. This number is used by applications to prevent duplicate page submissions and can be used for other purposes. For example, to make a unique URL and avoid browser caching issues, you can embed this number in the request or debug column in calls to the f procedure. Table 3-9 describes the supported syntax for referencing APP_UNIQUE_PAGE_ID.

The following is an HTML example:

SELECT 'f?p=100:1:'||:APP_SESSION||':'||:APP_UNIQUE_PAGE_ID||
    ':::P1_EMPNO:'||employee_id,
   first_name,
    job_id
FROM employees

Note the use of the APP_UNIQUE_PAGE_ID in the request column. This makes this URL unique and may avoid excessive browser caching problems.

APP_USER

APP_USER is the current user running the application. Depending upon your authentication model, the value of the user is set differently. If the application is running using database authentication, then the value of the user is the same as the database pseudo column USER. If the application uses an authentication scheme that requires the user to authenticate, the value of APP_USER is set by the authentication scheme, usually to the user name used during authentication. Table 3-10 describes the supported syntax for referencing APP_USER.

Consider the following examples:

• From within an HTML region:

  Hello you are logged in as &APP_USER.
  

• Using PL/SQL:

  htp.p('Hello you are logged in as'||V('APP_USER')); 
  

• As a bind variable:

  SELECT * FROM some_table WHERE user_id = :APP_USER
  

  See Also:

  "Authentication" for information about the Public User attribute

AUTHENTICATED_URL_PREFIX

This application-level attribute identifies a valid authenticated prefix (that is, a logged in URL prefix). You can use a relative path or a full path beginning with http. This item is useful if your application can be run in both authenticated (logged in) and public (not logged in) modes. You can use AUTHENTICATED_URL_PREFIX to construct a link to an authenticated page. This item is most useful when using basic database authentication because changes to the URL can require authentication. Table 3-11 describes the supported syntax for referencing AUTHENTICATED_URL_PREFIX.

BROWSER_LANGUAGE

BROWSER_LANGUAGE refers to the Web browser's current language preference. Table 3-12 describes the supported syntax for referencing BROWSER_LANGUAGE.

CURRENT_PARENT_TAB_TEXT

CURRENT_PARENT_TAB_TEXT is most useful in page templates, but is only relevant for applications that use two-level tabs (that is, parent and standard tabs). Use this string to reference the parent tab label. This substitution string enables you to repeat the currently selected parent tab within the page template. Table 3-13 describes the supported syntax for referencing CURRENT_PARENT_TAB_TEXT.

DEBUG

Valid values for the DEBUG flag are Yes or No. Turning debug on shows details about application processing. If you write your own custom code, you may want to generate debug information only if the debug mode is set to Yes. Table 3-14 describes the supported syntax for referencing DEBUG.

The following is an example of a substitution string reference that preserves the current value of DEBUG:

f?p=100:1:&APP_SESSION.::&DEBUG

HOME_LINK

HOME_LINK is the home page of an application. The Application Express engine will redirect to this location if no page is given and if no alternative page is dictated by the authentication scheme's logic. You define the Home Link on the Application Attributes page.

Table 3-15 describes the supported syntax for referencing HOME_LINK.

LOGIN_URL

Use LOGIN_URL to display a link to a login page for users that are not currently logged in. Table 3-16 describes the supported syntax for LOGIN_URL.

IMAGE_PREFIX

The value of IMAGE_PREFIX determines the virtual path the Web server uses to point to the images directory distributed with Oracle Application Express. To reference uploaded images, use WORKSPACE_IMAGES and APP_IMAGES. Table 3-17 describes the supported syntax for referencing IMAGE_PREFIX.

Application Express SCHEMA OWNER

If you are generating calls to applications from within your PL/SQL code, you may need to reference the owner of the Oracle Application Express schema. The following describes the correct syntax for a direct PL/SQL reference:

APEX_APPLICATION.G_FLOW_SCHEMA_OWNER

You may also use #FLOW_OWNER# to reference this value in SQL queries and PL/SQL (for example, in a region or a process).

PRINTER_FRIENDLY

The value of PRINTER_FRIENDLY determines if the Application Express engine is running in print view mode. This setting can be referenced in conditions to eliminate elements not desired in a printed document from a page. Table 3-18 describes the supported syntax for referencing PRINTER_FRIENDLY.

LOGOUT_URL

LOGOUT_URL is an application-level attribute used to identify the logout URL. This is a URL that navigates the user to a logout page or optionally directly logs out a user. To create a logout navigation bar entry, add a trailing period to &LOGOUT_URL (&LOGOUT_URL.). If you are coding a page template, use #LOGOUT_URL#. Table 3-19 describes the supported syntax for referencing LOGOUT_URL.

PROXY_SERVER

PROXY_SERVER is an application attribute. The attribute may be used by regions whose source comes from a URL. The following is the correct syntax for a direct PL/SQL reference used when you are writing PL/SQL to access remote Web servers from within the database (for example, when using the utl_http package shipped with the database).

APEX_APPLICATION.G_PROXY_SERVER

PUBLIC_URL_PREFIX

PUBLIC_URL_PREFIX is an application-level attribute that identifies a URL to toggle out of a logged in mode to a public view. Table 3-20 describes the supported syntax for referencing PUBLIC_URL_PREFIX.

REQUEST

Each application button sets the value of REQUEST to the name of the button or to the request value attribute associated with the button. This enables accept processing to reference the name of the button when a user clicks it. In the f?p syntax, REQUEST may be set using the fourth argument.

f?p=100:1:&APP_SESSION.:GO

IF V ('REQUEST') = 'GO' THEN
   htp.p('hello');
END IF;

SQLERRM

SQLERRM is a template substitution only available in the Applications Region Error Message. The following describes the correct syntax for a region template substitution reference:

#SQLERRM#

SYSDATE_YYYYMMDD

SYSDATE_YYYYMMDD represents the current date on the database server, with the YYYYMMDD format mask applied. You may use this value instead of repeated calls to the SYSDATE() function. The following list describes the supported syntax for referencing SYSDATE_YYYYMMDD.

• Bind variable

  :SYSDATE_YYYYMMDD
  

• PL/SQL

  V('SYSDATE_YYYYMMDD')
  

• Direct PL/SQL

  APEX_APPLICATION.G_SYSDATE (DATE DATATYPE)
  

WORKSPACE_IMAGES

Use this substitution string to reference uploaded images, JavaScript, and cascading style sheets that are shared over many applications within a workspace. Table 3-23 describes the supported syntax for referencing WORKSPACE_IMAGES.