Substitution Strings, Bind Variables, and Application Items: The Differences That Matter

Three Ways to Reference Values in APEX

APEX provides multiple syntaxes for referencing page items, application items, and other values. Understanding when to use each syntax is fundamental to writing correct, secure, and performant APEX applications. The three main syntaxes are bind variable syntax (:P10_NAME), substitution string syntax (&P10_NAME.), and the V() function.

Bind Variables: The Default Choice

Use colon syntax (:ITEM_NAME) in all PL/SQL code and SQL queries. This is the preferred and most secure approach because Oracle treats the value as a bind variable, which means it is immune to SQL injection and allows the database to cache and reuse query execution plans:

-- Correct: bind variable
SELECT employee_name
  FROM employees
 WHERE department_id = :P10_DEPT_ID;

-- PL/SQL process
BEGIN
    UPDATE employees
       SET salary = :P10_NEW_SALARY
     WHERE employee_id = :P10_EMP_ID;
END;

Substitution Strings: For Static Contexts

Use ampersand-dot syntax (&ITEM_NAME.) in contexts where bind variables are not supported, such as HTML regions, template text, and URL targets. The substitution happens at render time by literally replacing the token with the value. This is a text replacement, not a bind variable, so it does not protect against injection in SQL contexts:

<!-- In an HTML region -->
<h2>Welcome, &APP_USER.</h2>
<p>Your department: &P10_DEPT_NAME.</p>

<!-- In a URL target -->
f?p=&APP_ID.:20:&SESSION.::NO::P20_ID:&P10_SELECTED_ID.

Never use substitution strings in SQL or PL/SQL where bind variables are available. The literal text replacement creates SQL injection vulnerabilities and prevents execution plan sharing.

The V() and NV() Functions

V('ITEM_NAME') returns the VARCHAR2 value of a session state item, and NV('ITEM_NAME') returns the NUMBER value. These are useful in contexts where you need to reference APEX items from PL/SQL that is not running within an APEX context, such as database triggers or scheduled jobs:

-- In a database trigger (where :P10_USER wouldn't work)
SELECT V('APP_USER') INTO l_username FROM DUAL;

In normal APEX page processes, always prefer bind variables over V() because bind variables are more efficient. The V() function involves a function call for each reference, while bind variables are resolved directly from session state.

Quick Reference

Use :ITEM_NAME in PL/SQL and SQL (secure, efficient, cacheable). Use &ITEM_NAME. in HTML, templates, and URLs (text replacement, no injection protection). Use V('ITEM_NAME') only in PL/SQL running outside APEX context (database triggers, scheduled jobs). For any new code in an APEX process or region source, start with bind variables and only switch to another syntax if bind variables are not supported in that context.