2.9 Understanding Substitution Strings

This section describes how to use substitution strings within a page template or region source to replace a character string with another value. As you design your application and enable users to edit items, you use substitution strings to pass information.

2.9.1 About Using Substitution Strings

You can use substitution strings in App Builder in the following ways:

  • Include a substitution string within a template to reference component values

  • Reference page or application items using &ITEM. syntax

  • Use built-in substitution strings

Substitution Strings within Templates

Special substitution strings available within a template are denoted by the number symbol (#). For example:

#ABC#

Substitution Strings for Page or Application Items

To reference page or application items using substitution variables:

  1. Reference the page or application item in all capital letters.

  2. Precede the item name with an ampersand (&).

  3. Append a period (.) to the item name.

For example, you would refer to an application item named F101_X in a region, a region title, an item label, or in any of numerous other contexts in which static text is used, for example:

&F101_X.

Notice the required trailing period. When the page is rendered, Application Express engine replaces value the substitution string with the value of item F101_X.

2.9.2 Controlling Output Escaping in Substitution Strings

Developers can append an exclamation mark (!) followed by a predefined filter name to a page or application item name, report column, or other substitution strings to escape special characters in the substitution value. Output escaping is an important security technique to avoid Cross Site Scripting (XSS) attacks in the browser. Oracle Application Express already makes a best effort to automatically escape characters in a HTML or JavaScript context. With this extended syntax, developers have fine grained control over the output.

Available escape filters include:

  • HTML escapes reserved HTML characters, for example:

    &ITEM!HTML.
    #REPORT_COLUMN!HTML#
    

    See Also:

    "APEX_ESCAPE.HTML" in Oracle Application Express API Reference

  • ATTR escapes reserved characters in a HTML attribute context, for example:

     #REPORT_COLUMN!ATTR#
    

    See Also:

    "APEX_ESCAPE.HTML_ATTRIBUTE" in Oracle Application Express API Reference

  • JS escapes reserved characters in a JavaScript context, for example:

    &ITEM!JS.
    #REPORT_COLUMN!JS#
    

    See Also:

    "APEX_ESCAPE.JS_LITERAL" in Oracle Application Express API Reference

  • RAW preserves the original item value and does not escape characters, for example:

    &ITEM!RAW.
    #REPORT_COLUMN!RAW#
    
  • STRIPHTML removes HTML tags from the output and escapes reserved HTML characters, for example:

    &ITEM!STRIPHTML.
    #REPORT_COLUMN!STRIPHTML#
    

2.9.3 About Determining Substitution String Usage within Templates

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

2.9.4 Using Built-in Substitution Strings

App Builder supports many built-in substitution strings. You can reference these substitution strings to achieve specific types of functionality.

Tip:

Note that bind variable :USER has special meaning within the database. Also, the term Direct PL/SQL refers to PL/SQL that can be used in stored database objects such as procedures and functions.

2.9.4.1 APEX$ROW_NUM

APEX$ROW_NUM refers the currently processed row number of a submitted tabular form data. You can use this placeholder in validations, processes, and conditions associated with a tabular form to refer to the row number of the currently processed tabular form row. Table 2-4 describes the supported syntax for referencing APEX$ROW_NUM.

Table 2-4 APEX$ROW_NUM

Reference Type Syntax

Bind variable

:APEX$ROW_NUM

PL/SQL

V('APEX$ROW_NUM')

Substitution string

&APEX$ROW_NUM.

2.9.4.2 APEX$ROW_SELECTOR

Use APEX$ROW_SELECTOR in validations, processes, and conditions associated with a tabular form to refer to the row selector check box in a tabular form. This placeholder returns X if the tabular form row selector check box of the currently processed tabular form row is checked and NULL if it unchecked. Table 2-5 describes the supported syntax for referencing APEX$ROW_SELECTOR.

Table 2-5 APEX$ROW_SELECTOR

Reference Type Syntax

Bind variable

:APEX$ROW_SELECTOR

PL/SQL

V('APEX$ROW_SELECTOR')

Substitution string

&APEX$ROW_SELECTOR.

2.9.4.3 APEX$ROW_STATUS

Use APEX$STATUS in validations, processes, and conditions associated with a tabular form to refer to the row status in a tabular form. This placeholder returns the status of C if created, U if updated, or D if deleted for the currently processed tabular form row. Table 2-6 describes the supported syntax for referencing APEX$ROW_STATUS.

Table 2-6 APEX$ROW_STATUS

Reference Type Syntax

Bind variable

:APEX$ROW_STATUS

PL/SQL

V('APEX$ROW_STATUS')

Substitution string

&APEX$ROW_STATUS.

2.9.4.4 APP_ID

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

Table 2-7 APP_ID Syntax

Reference Type Syntax

Bind variable

:APP_ID

Direct PL/SQL

APEX_APPLICATION.G_FLOW_ID (A NUMBER)

PL/SQL

NV('APP_ID')

Substitution string

&APP_ID.

The following is an example of a substitution string reference:

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

2.9.4.5 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 2-8 describes the supported syntax for referencing APP_ALIAS.

Table 2-8 APP_ALIAS Syntax

Reference Type Syntax

Bind variable

:APP_ALIAS

PL/SQL

V('APP_ALIAS')

Substitution string

&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>

2.9.4.6 APP_AJAX_X01, ... APP_AJAX_X10

APP_AJAX_Xnn specifies the values of the APP_AJAX_X01, ... APP_AJAX_X10 URL parameters most recently passed to or set within the show or accept modules. You typically use these variables in On Demand AJAX processes.

Table 2-9 describes the supported syntax for referencing APP_AJAX_Xnn.

Table 2-9 APP_AJAX_Xnn Syntax

Reference Type Syntax

Bind variable

... :APP_AJAX_X01, ... :APP_AJAX_X10

PL/SQL

... v('APP_AJAX_X01'), ... v('APP_AJAX_X10')

Substitution string

... &APP_AJAX_X01., ... &APP_AJAX_X10.

See Also:

G_X01, ... G_X10 variables in the APEX_APPLICATION in Oracle Application Express API Reference

2.9.4.7 APP_DATE_TIME_FORMAT

APP_DATE_TIME_FORMAT is the application date time format of the application. This value reflects the format specified in the Application Date Time Format attribute of the Globalization settings of an application. If the Application Date Time Format is not set in an application, then a reference to APP_DATE_TIME_FORMAT returns the database session NLS date format and the NLS time format. Table 2-10 describes the supported syntax for referencing APP_DATE_TIME_FORMAT.

Table 2-10 APP_DATE_TIME_FORMAT Syntax

Reference Type Syntax

Bind variable

:APP_DATE_TIME_FORMAT

PL/SQL

V('APP_DATE_TIME_FORMAT')

Substitution string

&APP_DATE_TIME_FORMAT.

2.9.4.8 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 2-11 describes the supported syntax for referencing APP_IMAGES.

Table 2-11 APP_IMAGES Syntax

Reference Type Syntax

Bind variable

:APP_IMAGES

Direct PL/SQL

Not available.

PL/SQL

V('APP_IMAGES')

Substitution string

&APP_IMAGES.

Template substitution

#APP_IMAGES#

2.9.4.9 APP_NLS_DATE_FORMAT

APP_NLS_DATE_FORMAT is the application date format of the database session. This value reflects the format specified in the Application Date Format attribute of the Globalization settings of the application. However, if the Application Date Format is not set, then APP_NLS_DATE_FORMAT returns the NLS_DATE_FORMAT value of the database session at the start of the request to the Application Express engine. Table 2-12 describes the supported syntax for referencing APP_NLS_DATE_FORMAT.

Table 2-12 APP_NLS_DATE_FORMAT Syntax

Reference Type Syntax

Bind variable

:APP_NLS_DATE_FORMAT

PL/SQL

V('APP_NLS_DATE_FORMAT')

Substitution string

&APP_NLS_DATE_FORMAT.

2.9.4.10 APP_NLS_TIMESTAMP_FORMAT

APP_NLS_TIMESTAMP_FORMAT is the application timestamp format of the database session. This value reflects the format specified in the Application Timestamp Format attribute of the Globalization settings of the application. However, if the Application Timestamp Format is not set, then APP_NLS_TIMESTAMP_FORMAT return the NLS_TIMESTAMP_FORMAT value of the database session at the start of the request to the Application Express engine. Table 2-13 describes the supported syntax for referencing APP_NLS_TIMESTAMP_FORMAT.

Table 2-13 APP_NLS_TIMESTAMP_FORMAT Syntax

Reference Type Syntax

Bind variable

:APP_NLS_TIMESTAMP_FORMAT

PL/SQL

V('APP_NLS_TIMESTAMP_FORMAT')

Substitution string

&APP_NLS_TIMESTAMP_FORMAT.

2.9.4.11 APP_NLS_TIMESTAMP_TZ_FORMAT

APP_NLS_TIMESTAMP_TZ_FORMAT is the application timestamp time zone format of the database session. This value reflects the format specified in the Application Timestamp Time Zone Format attribute of the Globalization settings of an application. However, if the Application Timestamp Time Zone Format is not set, then APP_NLS_TIMESTAMP_TZ_FORMAT returns the NLS_TIMESTAMP_TZ_FORMAT value of the database session at the start of the request to the Application Express engine. Table 2-14 describes the supported syntax for referencing APP_NLS_TIMESTAMP_TZ_FORMAT.

Table 2-14 APP_NLS_TIMESTAMP_TZ_FORMAT Syntax

Reference Type Syntax

Bind variable

:APP_NLS_TIMESTAMP_TZ_FORMAT

PL/SQL

V('APP_NLS_TIMESTAMP_TZ_FORMAT')

Substitution string

&APP_NLS_TIMESTAMP_TZ_FORMAT.

2.9.4.12 APP_PAGE_ALIAS

APP_PAGE_ALIAS is an alphanumeric name for the current application page. A page alias is not case-sensitive and it is an optional page attribute. APP_PAGE_ALIAS is unique within an application. You can use APP_PAGE_ALIAS almost anywhere APP_PAGE_ID can be used. Table 2-16 describes the supported syntax for referencing APP_PAGE_ID.

Table 2-15 APP_PAGE_ALIAS Syntax

Reference Type Syntax

Bind variable

:APP_PAGE_ALIAS

PL/SQL

v('APP_PAGE_ALIAS')

Substitution string

&APP_PAGE_ALIAS.

The following is an HTML example:

The alias of the current page is: &APP_PAGE_ALIAS.

2.9.4.13 APP_PAGE_ID

APP_PAGE_ID is the current application page 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 must work generically in multiple applications. Table 2-16 describes the supported syntax for referencing APP_PAGE_ID.

Table 2-16 APP_PAGE_ID Syntax

Reference Type Syntax

Bind variable

:APP_PAGE_ID

PL/SQL

:APP_PAGE_ID

PL/SQL and Direct PL

NV('APP_PAGE_ID')

Substitution string

&APP_PAGE_ID.

The following is an example of a substitution string reference:

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

2.9.4.14 APP_REQUEST_DATA_HASH

APP_REQUEST_DATA_HASH is a hash value of the request, item name, and item value parts in the URL. It is primarily useful to detect whether two browser requests passed different parameters to APEX. Table 2-17 describes the supported syntax for referencing APP_REQUEST_DATA_HASH.

Table 2-17 APP_SESSION Syntax

Reference Type Syntax

Bind variable

:APP_REQUEST_DATA_HASH

PL/SQL

V('APP_REQUEST_DATA_HASH')

Substitution string

&APP_REQUEST_DATA_HASH.

2.9.4.15 APP_SESSION

APP_SESSION is 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 2-18 describes the supported syntax for referencing APP_SESSION.

Table 2-18 APP_SESSION Syntax

Reference Type Syntax

Bind variable

:APP_SESSION

PL/SQL

V('APP_SESSION')

Short PL/SQL

V('SESSION')

Substitution string

&APP_SESSION.

SYS_CONTEXT variable

SYS_CONTEXT('APEX$SESSION', '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,'click me') FROM DUAL;
    
  • Using the SYS_CONTEXT variable:

    SELECT ... WHERE apex_session_id = SYS_CONTEXT('APEX$SESSION', 'APP_SESSION')
    

    Oracle Application Express sets up the APEX$SESSION context when it starts to process an incoming request. For example, you can use the value of 'APP_SESSION' to access the current application session in queries and VPD (Virtual Private Database) security policies that protect your table data.

2.9.4.16 APP_SESSION_VISIBLE

APP_SESSION_VISIBLE is similar to the built-in substitution APP_SESSION. Use this substitution string to create hypertext links between application pages that maintain a session state by passing the session number. APP_SESSION_VISIBLE always returns '0' when users are not authenticated to an application and they are using the Zero Session ID feature of Oracle Application Express. Table 2-19 describes the supported syntax for referencing APP_SESSION_VISIBLE.

Table 2-19 APP_SESSION_VISIBLE Syntax

Reference Type Syntax

Bind variable

:APP_SESSION_VISIBLE

PL/SQL

V('APP_SESSION_VISIBLE')

Substitution string

&APP_SESSION_VISIBLE.

Consider the following examples:

  • From within an HTML region:

    <a href="f?p=100:5:&APP_SESSION_VISIBLE.">click me</a>
    
  • Using PL/SQL:

    sys.htf.anchor('f?p=100:5:'||V('APP_SESSION_VISIBLE'),'click me');
    
  • Using a SQL query:

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

2.9.4.17 APP_TITLE

APP_TITLE is an alphanumeric title for the current application. The title is derived from an application substitution string called APP_TITLE. If not defined the Logo attribute will be used if it is of type text. The last fallback is the application name. Table 2-20 describes the supported syntax for referencing APP_TITLE.

Table 2-20 APP_TITLE Syntax

Reference Type Syntax

Bind variable

:APP_TITLE

PL/SQL

v('APP_TITLE')

Substitution string

&APP_TITLE.

The following is an HTML example:

The title of the current application is: &APP_TITLE!HTML.

2.9.4.18 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 2-21 describes the supported syntax for referencing APP_UNIQUE_PAGE_ID.

Table 2-21 APP_UNIQUE_PAGE_ID Syntax

Reference Type Syntax

Bind variable

:APP_UNIQUE_PAGE_ID

PL/SQL

V('APP_UNIQUE_PAGE_ID')

Substitution string

&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.

2.9.4.19 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 2-22 describes the supported syntax for referencing APP_USER.

Table 2-22 APP_USER Syntax

Reference Type Syntax

Bind variable

:APP_USER

PL/SQL

V('APP_USER')

Substitution string

&APP_USER.

SYS_CONTEXT variable

SYS_CONTEXT('APEX$SESSION', '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
    
  • Using the SYS_CONTEXT variable:

    SELECT ... WHERE username = SYS_CONTEXT('APEX$SESSION', 'APP_USER')
    

    Oracle Application Express sets up the APEX$SESSION context when it starts to process an incoming request. For example, you can use the value of 'APP_USER' to access the current application user in queries and VPD (Virtual Private Database) security policies that protect your table data.

    See Also:

    "Authentication" for information about the Public User attribute

2.9.4.20 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 2-23 describes the supported syntax for referencing AUTHENTICATED_URL_PREFIX.

Table 2-23 AUTHENTICATED_URL_PREFIX Syntax

Reference Type Syntax

Bind variable

:AUTHENTICATED_URL_PREFIX

PL/SQL

V('AUTHENTICATED_URL_PREFIX')

Substitution string

&AUTHENTICATED_URL_PREFIX.

2.9.4.21 BROWSER_LANGUAGE

BROWSER_LANGUAGE refers to the web browser's current language preference. Table 2-24 describes the supported syntax for referencing BROWSER_LANGUAGE.

Table 2-24 BROWSER_LANGUAGE Syntax

Reference Type Syntax

Bind variable

:BROWSER_LANGUAGE

Direct PL/SQL

APEX_APPLICATION.G_BROWSER_LANGUAGE

PL/SQL

V('BROWSER_LANGUAGE')

Substitution string

&BROWSER_LANGUAGE.

2.9.4.22 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 2-25 describes the supported syntax for referencing CURRENT_PARENT_TAB_TEXT.

Table 2-25 CURRENT_PARENT_TAB_TEXT Syntax

Reference Type Syntax

Bind variable

Not Available.

Substitution string

&CURRENT_PARENT_TAB_TEXT.

2.9.4.23 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 2-26 describes the supported syntax for referencing DEBUG.

Table 2-26 DEBUG Syntax

Reference Type Syntax

Bind variable

:DEBUG

Direct PL/SQL

APEX_APPLICATION.G_DEBUG

PL/SQL

V('DEBUG')

Substitution string

&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

2.9.4.24 HOME_LINK

HOME_LINK is the home page of an application. The Application Express engine redirects 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 URL on the User Interface Details page.

Table 2-27 describes the supported syntax for referencing HOME_LINK.

Table 2-27 HOME_LINK Syntax

Reference Type Syntax

Direct PL/SQL

APEX_APPLICATION.G_HOME_LINK

PL/SQL

V('HOME_LINK')

Template Reference

#HOME_LINK#

Substitution String

&HOME_LINK.

See Also:

"Managing the Application User Interface" and "User Interface Details Page" for information about the Home URL attribute

2.9.4.25 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 2-28 describes the supported syntax for referencing IMAGE_PREFIX.

Table 2-28 IMAGE_PREFIX Syntax

Reference Type Syntax

Bind variable

:IMAGE_PREFIX

Direct PL/SQL

APEX_APPLICATION.G_IMAGE_PREFIX

PL/SQL

V('IMAGE_PREFIX')

Substitution string

&IMAGE_PREFIX.

Template Substitution

#IMAGE_PREFIX#

2.9.4.26 JET_BASE_DIRECTORY

Use the JET_BASE_DIRECTORY substitution string to reference the base directory of the Oracle JavaScript Extension Toolkit (JET) which ships with Oracle Application Express. Supported syntax for referencing JET_BASE_DIRECTORY :

#JET_BASE_DIRECTORY#

2.9.4.27 JET_CSS_DIRECTORY

Use the JET_CSS_DIRECTORY substitution string to reference the base directory of the Oracle JavaScript Extension Toolkit (JET) which ships with Oracle Application Express. Supported syntax for referencing JET_CSS_DIRECTORY:

#JET_CSS_DIRECTORY#

2.9.4.28 JET_JS_DIRECTORY

Use the JET_JS_DIRECTORY substitution string to reference the JavaScript directory of the Oracle JavaScript Extension Toolkit (JET) components which ships with Oracle Application Express. Supported syntax for referencing JET_JS_DIRECTORY:

#JET_JS_DIRECTORY#

2.9.4.29 LOGIN_URL

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

Table 2-29 LOGIN_URL Syntax

Reference Type Syntax

Bind variable

:LOGIN_URL

Direct PL/SQL

APEX_APPLICATION.G_LOGIN_URL

PL/SQL

V('LOGIN_URL')

Substitution string

&LOGIN_URL.

Template Substitution

#LOGIN_URL#

2.9.4.30 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 2-30 describes the supported syntax for referencing LOGOUT_URL.

Table 2-30 LOGOUT_URL Syntax

Reference Type Syntax

Bind variable

:LOGOUT_URL

PL/SQL

V('LOGOUT_URL')

Substitution string

&LOGOUT_URL.

Template substitution

#LOGOUT_URL#

2.9.4.31 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 2-31 describes the supported syntax for referencing PRINTER_FRIENDLY.

Table 2-31 PRINTER_FRIENDLY Syntax

Reference Type Syntax

Direct PL/SQL

APEX_APPLICATION.G_PRINTER_FRIENDLY (VARCHAR2 DATATYPE)

PL/SQL

V('PRINTER_FRIENDLY')

Substitution string

&PRINTER_FRIENDLY.

2.9.4.32 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

2.9.4.33 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 2-32 describes the supported syntax for referencing PUBLIC_URL_PREFIX.

Table 2-32 PUBLIC_URL_PREFIX Syntax

Reference Type Syntax

Bind variable

:PUBLIC_URL_PREFIX

PL/SQL

V('PUBLIC_URL_PREFIX')

Substitution string

&PUBLIC_URL_PREFIX.

Template substitution

#PUBLIC_URL_PREFIX#

2.9.4.34 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, enabling 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.

REQUEST is typically referenced during Accept processing (that is, the processing that occurs when you post a page). Table 2-33 describes the supported syntax for referencing REQUEST.

Table 2-33 REQUEST Syntax

Reference Type Syntax

Bind variable

:REQUEST

Direct PL/SQL

APEX_APPLICATION.G_REQUEST

PL/SQL

V('REQUEST')

Substitution string

&REQUEST.

See Also:

"Using REQUEST"

2.9.4.35 Using REQUEST

REQUEST is typically referenced during Accept processing (that is, the processing that occurs when you post a page). This section describes additional information about how to use the REQUEST substitution string.

See Also:

"REQUEST"

2.9.4.35.1 About Scope and Value of REQUEST for Posted Pages

When you post a page, you initiate Accept processing. Accept processing consists of computations, validations, processes, and branches. The value of REQUEST is available during each phase of the Accept processing. Once an application branches to a different page then REQUEST is set to NULL.

The value of REQUEST is the name of the button the user clicks, or the name of the tab the user selects. For example, suppose you have a button with a name of CHANGE, and a label Apply Change. When a user clicks the button, the value of REQUEST is CHANGE.

2.9.4.35.2 About the When Button Pressed Attribute

Validations, processes, and branches have a When Button Pressed attribute. This attribute displays as a select list and contains the names of buttons that exist on the current page. If you make a selection from When Button Pressed, you associate the button's REQUEST value with the validation, process, or branch.

When you use a button to submit a page, the REQUEST value is passed to the page. The Accept processing logic evaluates each validation, process, and branch that uses a When Button Pressed attribute to determine whether the component should run (or fire). When one of these components runs, do not assume that a user actually clicked the associated button and caused the page to be submitted. Keep in mind, that another button using the same request value may have submitted the page. Similarly, JavaScript on the page can also submit the page and pass in a request value.

2.9.4.35.3 About Referencing REQUEST Using Declarative Conditions

Many developers reference REQUEST using conditions. For example, you may want to reset pagination when a user clicks Go on a report page. You can reset pagination by creating an on-submit page process. The page process can be made conditional using the condition Request = Expression 1.

To conditionalize an on-submit page process:

  1. Under Condition, select the condition type Request = Expression 1.

  2. In Expression 1, enter GO.

2.9.4.35.4 About Using REQUEST for Show Processing

You can also use REQUEST for Show processing when navigating to a page using f?p syntax. For example:

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

Remember that the fourth argument in the f?p syntax is REQUEST. This example goes to application 100, page 1 for the current session, and sets the value of REQUEST to GO. Any process or region can reference the value of REQUEST using Show processing.

The following is a similar example using PL/SQL:

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

Note that htp.p('hello') is a call to a PL/SQL Web Toolkit package to print the specified text string.

2.9.4.35.5 About Using BRANCH_TO_PAGE_ACCEPT

You can use a special request BRANCH_TO_PAGE_ACCEPT for Show processing to automatically submit the page. For example:

f?p=100:1:&APP_SESSION.:BRANCH_TO_PAGE_ACCEPT|SAVE:::P1_DATA:value

Using BRANCH_TO_PAGE_ACCEPT is the same as navigating to page 1, entering a value into the item P1_DATA, and clicking a button that submits the page with a SAVE request.

2.9.4.36 SCHEMA OWNER

If you are generating calls to applications from within your PL/SQL code, you must 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).

2.9.4.37 SQLERRM

SQLERRM is a template substitution only available in the Applications Region Error Message. Supported syntax for a region template substitution reference:

#SQLERRM#

2.9.4.38 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)
    

Table 2-34 SYSDATE_YYYYMMDD Syntax

Reference Type Syntax

Bind variable

:SYSDATE_YYYYMMDD

Direct PL/SQL

APEX_APPLICATION.G_SYSDATE (DATE DATATYPE)

PL/SQL

V('SYSDATE_YYYYMMDD')

2.9.4.39 THEME_DB_IMAGES

Use the THEME_DB_IMAGES substitution string to always reference files which are stored with your theme definition in the database, regardless of the File Prefix" setting of your theme. Supported syntax for a template substitution:

#THEME_DB_IMAGES#

2.9.4.40 THEME_IMAGES

Use the THEME_IMAGES substitution string to reference files which are stored with your theme definition. Supported syntax for a template substitution:

#THEME_IMAGES#

2.9.4.41 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 2-35 describes the supported syntax for referencing WORKSPACE_IMAGES.

Table 2-35 WORKSPACE_IMAGES Syntax

Reference Type Syntax

Bind variable

:WORKSPACE_IMAGES

Direct PL/SQL

Not available

PL/SQL

V('WORKSPACE_IMAGES')

Substitution string

&WORKSPACE_IMAGES.

Template substitution

#WORKSPACE_IMAGES#

See Also:

"APP_IMAGES" and "IMAGE_PREFIX"

2.9.4.42 WORKSPACE_ID

Use this substitution string to reference the workspace ID. Table 2-36 describes the supported syntax for referencing WORKSPACE_ID.

Table 2-36 WORKSPACE_ID Syntax

Reference Type Syntax

Bind variable

:WORKSPACE_ID

PL/SQL

V('WORKSPACE_ID')

Substitution string

&WORKSPACE_ID.

SYS_CONTEXT variable

SELECT ... WHERE workspace_id = SYS_CONTEXT('APEX$SESSION', ''WORKSPACE_ID')

Consider the following examples:

  • From within an HTML region:

    Hello your workspace id is &WORKSPACE_ID.
    
  • Using PL/SQL:

    htp.p('Hello your workspace id is '||V('WORKSPACE_ID'));
    
  • Using a bind variable:

    SELECT * FROM some_table WHERE workspace_id = :WORKSPACE_ID
    
  • Using the SYS_CONTEXT variable:

    SELECT ... WHERE workspace_id = SYS_CONTEXT('APEX$SESSION', 'WORKSPACE_ID')
    

    Oracle Application Express sets up the APEX$SESSION context when it starts to process an incoming request. For example, you can use the value of 'WORKSPACE_ID' to access the current workspace ID value in queries and VPD (Virtual Private Database) security policies that protect your table data.