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

 Built-in substitution variables in Oracle APEX provide a powerful way to dynamically insert values, such as session information, application attributes, and user context, directly into page items, SQL queries, or PL/SQL code. These variables simplify application development by enabling flexible, context-aware behavior without requiring complex programming. Understanding how to create and effectively use these substitution variables allows developers to build more dynamic and personalized applications that respond intelligently to user and session data.

Built-in substitution variables in Oracle APEX are predefined placeholders that allow developers to dynamically insert values related to the current application, session, user, or environment directly into SQL queries, PL/SQL code, page items, or report templates. These variables provide a flexible mechanism to tailor the behavior and content of your application without hardcoding values, enhancing maintainability and user personalization.

To use built-in substitution variables, first understand the common types available, such as:

• APP_ID: The current application ID

• APP_USER: The current logged-in user’s name

• APP_SESSION: The current session ID

• APP_PAGE_ID: The current page number

• APP_SCHEMA: The database schema used by the application

For example, you can use substitution variables in a SQL query like this:

SELECT * FROM EMPLOYEES WHERE CREATED_BY = '&APP_USER.'  

Here, &APP_USER. is replaced at runtime with the username of the logged-in user, filtering results accordingly.

In Oracle APEX, substitution variables can be used in various places:

• SQL and PL/SQL Code: Embedding session or application context values dynamically.

• Page Items and Region Titles: To personalize labels or content based on user or session data.

• Templates and Messages: Displaying dynamic values within templates using substitution syntax.

To create custom substitution variables, developers can define application-level or page-level items and reference them similarly, expanding the flexibility beyond built-in variables.

When using substitution variables, note the syntax differences:

• &VARIABLE. for substitution in SQL and PL/SQL.

• :VARIABLE for bind variables in PL/SQL blocks.

Additionally, APEX supports escaping or setting default values to handle null or missing substitution values safely.

By leveraging built-in substitution variables, you ensure your application adapts dynamically to the current user and context, simplifying development and improving the user experience. Always test substitution expressions to verify that values resolve correctly during runtime.

In summary, built-in substitution variables are fundamental tools in Oracle APEX development, allowing you to inject dynamic content seamlessly. Their proper use leads to more maintainable, flexible, and user-aware applications without the need for complex code or manual updates.

Example

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.

In conclusion, mastering built-in substitution variables is essential for leveraging the full capabilities of Oracle APEX. By using these variables strategically, developers can enhance application flexibility, reduce repetitive coding, and improve maintainability. Proper use of substitution variables ensures that your APEX applications remain dynamic, responsive, and tailored to end-user needs while maintaining clear and efficient code.