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

 In Oracle APEX, built-in substitution variables play a critical role in creating dynamic and responsive applications. These variables act as placeholders that are automatically replaced at runtime with relevant context-based values, such as user session details, application information, page identifiers, and environment settings. By using built-in substitution variables, developers can build pages that adapt to users, simplify logic across applications, and reduce hardcoded values. Whether you're customizing page titles, building conditional logic, or referencing application metadata, substitution variables streamline development and enhance maintainability.

In Oracle APEX, built-in substitution variables are predefined variables that allow you to access runtime values such as user session details, page metadata, and environment-specific properties. These variables can be used in SQL queries, PL/SQL code, HTML templates, JavaScript, conditions, and other dynamic expressions across your application. They provide a powerful way to make your application context-aware, dynamic, and more maintainable without hardcoding values.

How to Use Substitution Variables in Oracle APEX

1. Syntax of Substitution Variables
Built-in substitution variables follow the format &VARIABLE_NAME. when used in static text (such as HTML templates) and :VARIABLE_NAME when used in PL/SQL or SQL.

Example (in SQL or PL/SQL expressions):

:APP_USER

Example (in HTML or templates):

Welcome, &APP_USER.!

2. Common Built-in Substitution Variables

• APP_USER – Returns the username of the current logged-in user.

• APP_ID – Returns the numeric ID of the current application.

• APP_NAME – Returns the name of the current application.

• APP_PAGE_ID – Returns the ID of the current page.

• APP_SESSION – Returns the session ID for the user.

• APP_ALIAS – Returns the alias of the application (e.g., F?p alias).

• WORKSPACE_NAME – Returns the name of the current workspace.

• APP_IMAGES – Returns the path to the application images.

• APP_DATE_TIME_FORMAT – Returns the default date and time format.

• DEBUG – Returns YES if debug mode is enabled, else NO.

3. Where to Use Them

• Page Titles and Regions
  You can use &APP_USER. in the page title or region headings to personalize the user interface.

• HTML Expressions in Classic Reports
  Embed dynamic content based on session values:

  <b>Submitted by: &APP_USER.</b>
  

• Conditions
  Show or hide buttons, regions, or items based on the logged-in user:

  :APP_USER = 'ADMIN'
  

• PL/SQL Process Code
  Use these variables to log or branch based on application ID, user role, or session data.

4. Example: Conditional Display Based on User
In a button or region, set a Server-side Condition of type PL/SQL Expression:

:APP_USER = 'HR_MANAGER'

This ensures that only the HR_MANAGER sees the component.

5. Example: Logging Page Access
You can create a process that logs the page access using:

INSERT INTO page_log (app_id, page_id, user_name, access_time)
VALUES (:APP_ID, :APP_PAGE_ID, :APP_USER, SYSDATE);

6. Substitution Variables in URLs
You can pass and use values in the URL like:

f?p=100:1:&APP_SESSION.::NO::P1_ITEM_ID:123

In this case, &APP_SESSION. ensures the session is maintained when navigating pages.

7. Notes and Best Practices

• Always differentiate between & (substitution) and : (bind) variables.

• Substitution variables are resolved before the page is rendered, whereas bind variables are used during SQL execution.

• Avoid using substitution variables directly in SQL queries where bind variables are safer (to prevent SQL injection).

By using built-in substitution variables effectively, you reduce redundancy, increase maintainability, and allow the application to respond dynamically to the current context of the user and session.

Examples

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.

Understanding how and where to use these variables can significantly improve the efficiency and clarity of your APEX applications. From referencing APP_USER to identify the logged-in user, to using APP_PAGE_ID for context-sensitive conditions, substitution variables allow you to embed intelligence directly into SQL, PL/SQL, HTML, and JavaScript within your application. By mastering these built-in tools, developers can build smarter, more secure, and scalable applications that respond automatically to user context and application behavior.

How do I Create APEX Item and Column Properties

 Introduction

Creating APEX Item and Column Properties is an essential skill when building dynamic, user-friendly applications in Oracle APEX. These properties determine how individual items (like form fields) and report columns behave and appear within your app. By setting properties such as default values, validations, read-only status, or conditional visibility, you can fine-tune the user experience and maintain data integrity. Whether you're working on a form or a report, understanding how to configure these settings ensures that your application logic is applied consistently and effectively across different components.

In Oracle APEX, creating and configuring item and column properties is key to building responsive, user-friendly, and secure applications. APEX items represent form fields such as text fields, checkboxes, select lists, date pickers, and more. Column properties apply to reports such as Classic Reports, Interactive Reports, and Interactive Grids. These settings help define how data is displayed, validated, and interacted with across your application.

To create and configure an APEX item, go to:

App Builder > Your Application > Page Designer

In Page Designer, you'll see three panes: Rendering, Layout, and Property Editor.

1. Add or select an item
In the Rendering tree, under a region (like a form region), right-click and choose “Create Page Item,” or select an existing item to modify.

2. Configure item properties using the Property Editor:

• Name: The internal name of the item (used in PL/SQL or conditions)

• Type: Choose from Text Field, Select List, Date Picker, etc.

• Label: User-facing label

• Value Required: Yes/No, to make it mandatory

• Default Value: Static value or use a SQL query/PLSQL function

• Read Only / Disabled: Prevent user edits

• Source Type / Source Value: Defines where the item gets its value (e.g., DB column, static value, or function)

• Session State Protection: Optional, restricts manipulation via URL

• Display Settings: Alignment, width, templates, etc.

3. Add validations and dynamic actions to enhance item behavior:

• Validations: For required fields, format checks, value ranges.

• Dynamic Actions: Change other fields, hide/show items, set values, refresh regions, etc., based on user interaction.

To define Column Properties in a report (e.g., Classic Report):

App Builder > Your Application > Page Designer > Select Report Region

1. Select a report column under the region in the Rendering tree.

2. Modify column properties in the Property Editor:

• Heading: Change the display name

• Type: Plain Text, Link, Hidden, Display as Image, etc.

• Alignment: Left, Center, Right

• Format Mask: For dates, numbers, currency

• Escape special characters: To allow HTML tags

• Link target: Add link to another page (detail view or edit form)

• Conditional Display: Show or hide column based on logic

• CSS Classes and Styles: Apply custom design using themes or inline CSS

You can also use the Column Attributes section to apply advanced behaviors like:

• Tooltip text

• Conditional formatting

• Aggregation (sum, count) for Interactive Reports or Grids

• Row highlight rules

Dynamic Properties via PL/SQL or Conditions
You can use conditions or PL/SQL expressions to make properties dynamic. For example:

• Set Read Only When = :P1_MODE = 'VIEW'

• Use a Display Condition like :P1_ROLE = 'ADMIN'

This makes your application reactive and context-aware, showing or hiding elements based on user roles, actions, or data.

By carefully setting and customizing APEX item and column properties, you ensure that your application's UI is functional, intuitive, and secure. It reduces manual coding, enforces business rules, and enhances the user experience across your entire application.

Example

In Oracle APEX, you can customize how form items and report columns are displayed by using various properties. These properties allow you to modify labels, display values, disable/enable items, manage CSS classes, and determine required fields, among other things.

In this tutorial, we will walk you through how to use the Item Properties and Column Properties in Oracle APEX with practical examples. We'll cover the following properties:

• Item Properties:

• LABEL

• DISPLAY

• CHANGED

• DISABLED

• Column Properties:

• HEADING

• LABEL

• DISPLAY

• HEADING_CLASS

• COLUMN_CLASS

• REQUIRED

Step 1: Understanding Item Properties

When working with form items (such as text fields, drop-down lists, checkboxes, etc.) in APEX, you have several important properties that control their behavior and appearance. Below, we will explain how to use these properties in your application.

1. Item Property: LABEL

• Purpose: The LABEL property defines the text label that appears next to a form item (such as a text field or drop-down). This is typically used to explain what the input is for.

• Usage: You can set this property when configuring a form item.

Example:

• Create an item like Text Field for First Name.

• In the Label field, enter "First Name". This will show up next to the input field on the form.

2. Item Property: DISPLAY

• Purpose: The DISPLAY property defines the current value that is shown in the form item when the page is rendered.

• Usage: This property can be set dynamically using SQL queries or PL/SQL.

Example:

• Create a Text Field for First Name.

• Set its Value property to SELECT first_name FROM employees WHERE employee_id = :P1_EMPLOYEE_ID.

• When the page loads, the first name of the employee with P1_EMPLOYEE_ID will be displayed in the text field.

3. Item Property: CHANGED

• Purpose: The CHANGED property indicates whether the value of an item has been modified. It will return "Y" if the value has changed from its default value and "N" if it hasn’t been changed.

• Usage: This is useful for validation or to trigger certain actions when a field is edited.

Example:

• Create a Text Field for Email Address.

• Add the following Dynamic Action:

• Event: Change

• Action: Display a message if the Email Address has changed.

• Condition: CHANGED = "Y"

4. Item Property: DISABLED

• Purpose: The DISABLED property controls whether the form item is editable. If set to "Y", the item will be disabled, preventing the user from modifying its value.

• Usage: This can be used in scenarios where certain form items should not be modified by the user.

Example:

• Create a Text Field for Employee ID.

• Set the Disabled property to "Y" for Employee ID, making it non-editable. The value will still be visible, but the user cannot modify it.

Step 2: Understanding Column Properties

When dealing with reports, you can customize the appearance and behavior of columns by setting various column properties. These properties help define the label, heading, required fields, and other visual elements for the columns in your report.

1. Column Property: HEADING

• Purpose: The HEADING property defines the column heading text that appears at the top of each column in a report.

• Usage: You can set this property to a custom string or use it to include HTML tags or other formatting.

Example:

• For a Classic Report, the column First Name can have the heading Employee First Name.

• In the Column Attributes section, set the Heading to: <b>Employee First Name</b> (using HTML tags for bold text).

2. Column Property: LABEL

• Purpose: The LABEL property defines the text label for each column. If no specific label is set, the column HEADING is used.

• Usage: This is often used when you want to provide a different label from the heading for columns.

Example:

• For the First Name column in a report, you can set the Label to "Name". The Heading would still be "Employee First Name", but the column would display as "Name" in the report.

3. Column Property: DISPLAY

• Purpose: The DISPLAY property controls how the value for a column is displayed in the report.

• Usage: This property can be customized using SQL expressions, PL/SQL, or APEX substitution variables to format the data as per the requirements.

Example:

• For a Salary column in a report, you can format the display value to show the salary in a specific format (e.g., including currency symbol).

• TO_CHAR(salary, '999,999.99')

4. Column Property: HEADING_CLASS

• Purpose: The HEADING_CLASS property allows you to add custom CSS classes to the column heading.

• Usage: This property is useful when you want to style the column headings with specific CSS rules.

Example:

• In the Column Attributes, you can add a CSS class to the HEADING_CLASS property like class="heading-blue".

• In the CSS file, define:

.heading-blue {

    color: blue;

    font-weight: bold;

}

5. Column Property: COLUMN_CLASS

• Purpose: The COLUMN_CLASS property lets you add CSS classes to the column cells in a report.

• Usage: This property is useful when you want to style the data in a particular column based on certain conditions.

Example:

• For the Salary column, you can set the COLUMN_CLASS property to class="salary-column".

• Then, in your CSS file, you can define a specific style:

.salary-column {

    font-weight: bold;

    color: green;

}

6. Column Property: REQUIRED

• Purpose: The REQUIRED property determines whether a column in a form is mandatory for users to fill out before submitting the form.

• Usage: This property is particularly useful for form items in APEX. If set to "Y", the column is marked as required.

Example:

• For a Text Field for Employee ID, set the Required property to "Y" to make it a mandatory field.

• The form will display a red asterisk next to the input field to indicate that it’s required.

Step 3: Practical Example – Using Item and Column Properties

Let's combine the item and column properties in a practical example, where we create a form and a report that display dynamic data.

1. Create a Form Page:

• Go to App Builder, and create a new Form page.

• Add a Text Field for Employee ID:

• Label: "Employee ID"

• Display: A SQL query to fetch the employee’s ID based on a session value.

• Disabled: "Y" (making it non-editable)

• Add a Text Field for Employee Name:

• Label: "Employee Name"

• Required: "Y" (making it mandatory)

• Add a Select List for Job Title:

• Label: "Job Title"

• Display: A dynamic list query to display job titles.

• Required: "Y"

2. Create a Report Page:

• Add a Classic Report to the page.

• Set the SQL Query to retrieve employee information:

SELECT employee_id, first_name || ' ' || last_name AS employee_name, job_title, salary

FROM employees

• Configure the column properties:

• Heading: "Employee Information"

• Label: "Full Name"

• Column Class: class="employee-column"

• Required: "N" (not applicable for reports)

• Customize the Salary column:

• Heading: "Annual Salary"

• Display: Format the salary column as currency using TO_CHAR(salary, '999,999.99').

3. Styling:

• Add custom CSS for the report and form:

.employee-column {

    font-size: 14px;

    font-weight: bold;

}

Conclusion

Mastering APEX Item and Column Properties allows developers to create more intelligent, interactive, and user-centric applications. From setting required fields and default values to applying dynamic actions and display logic, these properties offer robust control over how users interact with your pages. Taking full advantage of these settings leads to cleaner forms, smarter reports, and a more polished application overall. With these tools at your fingertips, you can deliver powerful APEX solutions that meet both user and business requirements with precision.