How to Create and Use Built-in Substitution Variables in Oracle APEX

Introduction
Oracle APEX includes a powerful feature called substitution variables that allows developers to insert dynamic values into SQL, PL/SQL, HTML, and JavaScript. These variables provide flexibility in referencing session state, system values, and application-level information without hardcoding. Built-in substitution variables come pre-defined and can be used throughout your application to simplify coding, personalize the user experience, and support debugging and navigation.

How to Create and Use Built-in Substitution Variables in Oracle APEX

Built-in substitution variables are predefined by APEX and are automatically available in your application without requiring explicit creation. They are used by placing them within & delimiters, such as &APP_USER. or &APP_ID..

1. Common Built-in Substitution Variables

   • &APP_USER. – The currently logged-in username

   • &APP_ID. – The Application ID

   • &APP_PAGE_ID. – The current page number

   • &APP_ALIAS. – The application alias

   • &APP_SESSION. – The session ID

   • &APP_IMAGES. – Path to the images directory

   • &PRINTER_FRIENDLY. – Whether the page is in printer-friendly mode (YES or NO)

2. Where You Can Use Them

   • In SQL queries (e.g., WHERE created_by = '&APP_USER.')

   • In PL/SQL blocks

   • In static content (e.g., region HTML or labels)

   • In URLs to pass parameters between pages (e.g., f?p=&APP_ID.:10:&APP_SESSION.::NO::P10_ID:123)

   • In dynamic actions and JavaScript expressions

3. Usage Examples

   • Displaying the logged-in user on the page:
     Create a Static Content region with the text: Welcome, &APP_USER.!

   • Using in a SQL report:

     SELECT * FROM orders WHERE sales_rep = '&APP_USER.'
     

   • Creating links between pages:
     Use the Link column attribute to set a target using:
     f?p=&APP_ID.:2:&APP_SESSION.::NO::P2_ID:#ID#

4. Best Practices

   • Always terminate substitution variables with a dot . to avoid conflicts

   • Use htf.escape_sc or APEX functions to escape values if used in HTML output

   • For security and clarity, don’t rely on substitution in sensitive queries; use bind variables where appropriate

   • Test how values render at runtime to avoid unexpected behaviors

 

In Oracle APEX, substitution variables allow you to dynamically insert values from the APEX environment into your pages, regions, and other components. These variables make it easier to access important application and session data in your templates, SQL queries, and dynamic actions.

In this tutorial, we will explore the built-in substitution variables available in Oracle APEX, including:

• &APP_USER.

• &APP_ID.

• &APP_PAGE_ID.

• &APP_SESSION.

• &APP_FILES.

• &WORKSPACE_FILES.

• &REQUEST.

• &DEBUG.

• &APEX_FILES.

• &IMAGE_PREFIX. (legacy - use &APEX_FILES. instead)

• &APEX_VERSION.

• &APEX_BASE_VERSION.

We will discuss how to use these substitution variables in real-life scenarios with examples.

Step 1: Understanding the Built-in Substitution Variables

Here is a breakdown of the built-in substitution variables available in Oracle APEX:

1. &APP_USER.

• Purpose: Represents the logged-in user’s username.

• Usage: You can use this variable to customize content based on the logged-in user.

SELECT * FROM users WHERE username = '&APP_USER.';

2. &APP_ID.

• Purpose: Represents the application ID of the current APEX application.

• Usage: Useful when you need to reference or log the application ID dynamically.

SELECT * FROM application_info WHERE app_id = '&APP_ID.';

3. &APP_PAGE_ID.

• Purpose: Represents the current page ID.

• Usage: You can use this variable to create page-specific content or for debugging purposes.

SELECT page_name FROM pages WHERE page_id = '&APP_PAGE_ID.';

4. &APP_SESSION.

• Purpose: Represents the current session ID.

• Usage: Useful when you need to log or track sessions or pass session-related data in URLs.

SELECT * FROM session_logs WHERE session_id = '&APP_SESSION.';

5. &APP_FILES.

• Purpose: Points to the directory where files associated with the APEX application are stored.

• Usage: You can use it when you need to reference or store files in your APEX application.

<a href="&APP_FILES./myfile.pdf">Download PDF</a>

6. &WORKSPACE_FILES.

• Purpose: Represents the directory where files associated with the workspace are stored.

• Usage: This is used when referencing files that are specific to the workspace.

<img src="&WORKSPACE_FILES./images/logo.png" alt="Logo">

7. &REQUEST.

• Purpose: Represents the current request parameter (e.g., GET or POST request).

• Usage: Useful for passing or reading dynamic values from the URL or form submission.

SELECT * FROM logs WHERE request_id = '&REQUEST.';

8. &DEBUG.

• Purpose: Indicates whether the debug mode is enabled ("YES") or disabled ("NO").

• Usage: You can use this to conditionally display debug information.

IF '&DEBUG.' = 'YES' THEN

  -- Show detailed debug information

END IF;

9. &APEX_FILES.

• Purpose: Represents the folder used for application-related files (this is often the same as &APP_FILES. but is more specifically used for APEX-related files).

• Usage: You can use this when referencing or storing files related to APEX applications.

<img src="&APEX_FILES./uploads/image.jpg" alt="Image">

10. &IMAGE_PREFIX. (legacy - use &APEX_FILES. instead)

• Purpose: The legacy prefix for images in your APEX application, now replaced by &APEX_FILES..

• Usage: You can still find it in older APEX versions, but it is now recommended to use &APEX_FILES..

11. &APEX_VERSION.

• Purpose: Represents the current version of Oracle APEX.

• Usage: Use this to display or log the APEX version for tracking purposes.

SELECT * FROM version_info WHERE apex_version = '&APEX_VERSION.';

12. &APEX_BASE_VERSION.

• Purpose: Represents the base version of Oracle APEX (e.g., the version of the software before any patches).

• Usage: Use this for compatibility or version tracking.

SELECT * FROM patch_info WHERE base_version = '&APEX_BASE_VERSION.';

---

Step 2: Practical Examples of Using Substitution Variables

Now that we know what each substitution variable does, let’s look at some practical examples of how to use them in APEX.

Example 1: Custom Greeting for Logged-in User

You can use the &APP_USER. substitution variable to create a personalized greeting for the logged-in user.

1. Create a Region in your APEX application (e.g., a Static Content region).

2. In the HTML Expression field, use the following code:

<h1>Welcome back, &APP_USER.!</h1>

3. When the page is rendered, it will display:

4. Welcome back, JOHN_DOE!

(assuming JOHN_DOE is the logged-in user).

Example 2: Displaying Application and Session Information

You can display the current application and session ID using the &APP_ID. and &APP_SESSION. variables.

1. Create a Static Content Region.

2. In the HTML Expression field, add the following code:

<p>Application ID: &APP_ID.</p>

<p>Session ID: &APP_SESSION.</p>

3. When the page loads, it will display:

4. Application ID: 100

5. Session ID: 123456789

Example 3: Debugging Mode Information

You can conditionally display debug information if the debug mode is enabled by using the &DEBUG. variable.

1. Create a Dynamic Action or PL/SQL Code on your page to check for debug mode:

BEGIN

  IF '&DEBUG.' = 'YES' THEN

    -- Display additional debug information

    htp.p('Debug Mode is enabled.');

  END IF;

END;

2. When Debug Mode is enabled, it will show:

3. Debug Mode is enabled.

Example 4: Custom File References Using &APP_FILES.

If you are working with files in your APEX application, you can reference them dynamically using &APP_FILES..

1. Suppose you have a PDF document stored in the Files directory.

2. You can create a link to download the file:

3. <a href="&APP_FILES./mydocument.pdf">Download Document</a>

4. When the page is rendered, the link will point to the correct location based on the application’s file directory.

Example 5: Displaying the APEX Version

You can display the current version of Oracle APEX using &APEX_VERSION. to inform users about the version running on the server.

1. Create a Static Content Region.

2. Add the following HTML expression:

3. <p>You are using Oracle APEX version &APEX_VERSION.</p>

4. When the page loads, it will display:

5. You are using Oracle APEX version 22.1.

Step 3: Using Substitution Variables in URLs

Substitution variables can also be useful when constructing dynamic URLs. For example, to navigate to a different page in your application, you can create a link that uses substitution variables to pass session and page information.

Example: Dynamic URL Based on Session ID

Suppose you want to create a link that includes the current session ID as part of the URL.

1. Create a Link in a region or item:

2. <a href="f?p=&APP_ID.:2:&APP_SESSION.:NO::&REQUEST.">Go to Page 2</a>

3. When the user clicks the link, it will navigate to Page 2 of the current application, passing the current session ID and request value.

Conclusion
Built-in substitution variables in Oracle APEX offer a simple yet powerful way to incorporate session, user, and application context throughout your application. They enable you to personalize experiences, build dynamic URLs, and maintain readable code. When used correctly, these variables reduce development time and ensure your application behaves consistently across sessions and users. Understanding where and how to apply them is an essential skill for every APEX developer.