Using Substitution Strings in Oracle APEX

Substitution strings in Oracle APEX are placeholders that enable you to dynamically insert values into your application’s text, SQL, PL/SQL, and other components at runtime. They provide a powerful way to customize content and behavior without hardcoding values, allowing for more flexible and maintainable applications. Understanding how to use substitution strings effectively is essential for any Oracle APEX developer.

How to Use Substitution Strings in Oracle APEX

Substitution strings use the syntax &STRING_NAME. to represent a placeholder that Oracle APEX replaces with a corresponding value during page rendering or code execution. These strings can reference various elements such as page items, application-level items, request parameters, or even custom substitution variables defined in your application.

Basic Usage Example:

If you have a page item named P1_USERNAME, you can reference its value using a substitution string like this:

Hello, &P1_USERNAME.

When the page renders, Oracle APEX substitutes &P1_USERNAME. with the current value of that page item.

Types of Substitution Strings

1. Page Item Substitution: References a page-level item value.

   • Example: &P1_ITEM_NAME.

2. Application Item Substitution: References an application-level item value.

   • Example: &APP_USER.

3. Built-in Substitutions: Oracle APEX defines built-in substitution strings such as &APP_ID., &APP_NAME., &APP_USER., etc.

4. Custom Substitution Strings: You can define your own substitution strings at the application or page level.

Using Substitution Strings in SQL and PL/SQL

Substitution strings can be used inside SQL queries or PL/SQL blocks to inject dynamic values:

SELECT * FROM employees WHERE department_id = &P2_DEPT_ID.

Note: When using substitution strings in SQL or PL/SQL, you must be careful as improper use can lead to SQL injection vulnerabilities. Use bind variables (:P2_DEPT_ID) where possible for better security and performance.

Using Substitution Strings in Page Text and Labels

You can use substitution strings in page item labels, region titles, and other text areas to personalize the interface based on the current user or session:

Welcome, &APP_USER., to the dashboard!

Substitution strings in these contexts are replaced at runtime with their current values.

Escaping Substitution Strings

If you need to display a literal ampersand & or a string that looks like a substitution string without substitution occurring, you can escape it by doubling the ampersand:

This will show &P1_ITEM_NAME. literally as &&P1_ITEM_NAME.

Best Practices for Using Substitution Strings

• Prefer using bind variables (:P1_ITEM) over substitution strings in SQL and PL/SQL to avoid SQL injection and improve performance.

• Use substitution strings primarily in text, labels, and static content where bind variables are not applicable.

• Validate and sanitize any values that might be substituted into dynamic SQL or PL/SQL to prevent security risks.

• Use meaningful and consistent naming conventions for page and application items to avoid confusion.

• Avoid excessive or unnecessary use of substitution strings to keep your application maintainable.

• Test substitution strings thoroughly to ensure correct values are substituted at runtime.

Examples

1. Using substitution in a region title:

Region Title: Sales Report for &P3_REGION_NAME.

2. Using substitution in a SQL query (with caution):

SELECT * FROM orders WHERE status = '&P4_ORDER_STATUS.'

3. Displaying current user in a page:

Logged in as: &APP_USER.

Substitution strings are used to replace or pass character strings within the application.

You can use a substitution string in page and application items and it always follows this format and these rules:

• Always starts with an ampersand (&).

• Always use capital letters.

• Always terminate the name with a  period(.).

Example:

&PXX_SOME_ITEM_NAME.

But for some other grid items it must be called as: # NAME_OF_COLUMN#

Substitution Strings can be used in the following:

• Interactive Grids

• Map Columns

• Cards

• Templates

Interactive grid syntax: &NAME_OF_COLUMN.

Syntax for other reports: # NAME_OF_COLUMN#

For Special Characters or Case Sensitive replacements use the  double quotes (“”)

Syntax is as follow: &”<name>”[!<format>].

• If the name is not quoted then it must be in the following range of characters A-Z-0-9_$#

• The name must be an application or page item.

• <format> refers to  predefined filtered names used for controlling or escaping.

• HTML

• ATTR

• JS

• RAW

• STRIPHTML

Example:

&”SOME_ITEM_NAME”.

Oracle APEX Documentation Link

For more detailed information on substitution strings, see the official Oracle APEX documentation:
https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/using-substitution-strings.html

Conclusion

Substitution strings in Oracle APEX are a versatile feature that allows you to inject dynamic content and values throughout your application. When used appropriately and securely, they enhance the flexibility and user experience of your applications. By understanding the types, syntax, and best practices for substitution strings, you can create more dynamic and personalized Oracle APEX applications.