BINDING AND SUBSTITUTION SYNTAX

Referencing Item Values in Oracle APEX

When working with item values in Oracle APEX, it's important to understand the correct syntax based on context. Different environments require different ways to reference item values, whether you're writing SQL queries, PL/SQL blocks, or setting values dynamically.

Syntax for Referencing Item Values

1. SQL Context

• Syntax: :MY_ITEM

• Use: Standard bind variable syntax for referencing items in SQL queries or inside PL/SQL blocks (within APEX).

• Notes: Use this when the item name is 30 characters or fewer. This is the preferred method in SQL.

2. PL/SQL Context – Character Items

• Syntax: V('MY_ITEM')

• Use: Use this inside PL/SQL code such as packages, functions, or stored procedures when referencing character item values.

• Notes: Avoid using this inside SQL queries, as it may lead to performance issues.

3. PL/SQL Context – Numeric Items

• Syntax: NV('MY_NUMERIC_ITEM')

• Use: Use this when referencing numeric items inside PL/SQL code (outside of SQL statements).

• Notes: As with V(), avoid using NV() in SQL queries to maintain performance.

4. Static Substitution in Text

• Syntax: &MY_ITEM.

• Use: Used for exact text substitution in HTML or static regions.

• Notes: Avoid using this in SQL or PL/SQL, as it may expose your app to SQL injection vulnerabilities.

Setting Item Values in Oracle APEX

You can set item values in several ways, depending on where and when you need the value to change.

1. Set Value Using the Source Attribute (Page Items)

• Navigate to the Page Designer.

• Click on the item name.

• Scroll to the Source section.

• Set the value using the appropriate source type (static value, SQL query, PL/SQL function, etc.).

2. Set Value Using PL/SQL Code

Example:

BEGIN
  :MY_ITEM := 'new value';
END;

Use this in PL/SQL blocks, such as page processes or validations.

3. Pass Value Through URL (f?p Syntax)

Example:

f?p=100:101:10636547268728380919::NO::MY_ITEM:ABC

This method passes the value ABC to the item MY_ITEM when calling the application via URL.

4. Use a Computation

Computations are useful for dynamically setting values during page processing.

Example:

TO_CHAR(SYSDATE, 'Day DD Month, YYYY')

Create a computation on the item to set its value at runtime.

5. Use the PL/SQL API (APEX_UTIL or APEX_SESSION)

Example using APEX_SESSION:

APEX_SESSION.SET_ITEM('MY_ITEM', 'new value');

Use this when setting item values programmatically, especially from packages or procedures running outside the page process.

Best Practices

• Use :ITEM_NAME for most SQL and PL/SQL code inside APEX.

• Avoid V() and NV() in SQL contexts to prevent performance degradation.

• Do not use &ITEM. substitution inside SQL or PL/SQL to avoid SQL injection.

• Always validate and sanitize input values when setting them dynamically.

• Leverage page computations and APEX built-in APIs for clean, maintainable logic.

Here's the rewritten version in plain text using clear structure and plain formatting, suitable for an Oracle APEX blog:

Referencing and Setting Item Values in Oracle APEX

In Oracle APEX, referencing item values correctly is essential when writing SQL queries, PL/SQL code, or setting values dynamically in the application. The syntax used depends on the context—whether you’re in SQL, PL/SQL, or static regions.

Referencing Item Values: Syntax Overview

1. In SQL Context

• Syntax: :MY_ITEM

• Use: Standard bind variable format. Recommended for referencing item values in SQL statements or PL/SQL blocks.

• Note: This method works when the item name is 30 bytes or fewer.

2. In PL/SQL Code (Character Values)

• Syntax: V('MY_ITEM')

• Use: Use this when accessing item values in PL/SQL procedures, functions, or packages.

• Avoid: Do not use this in SQL queries. It can negatively impact performance.

3. In PL/SQL Code (Numeric Values)

• Syntax: NV('MY_NUMERIC_ITEM')

• Use: Ideal for retrieving numeric item values within PL/SQL contexts.

• Avoid: Like V(), avoid this in SQL statements to maintain good performance.

4. In Static Text Regions

• Syntax: &MY_ITEM.

• Use: This is for exact text substitution in HTML or static text content.

• Caution: Do not use this in SQL or PL/SQL. It may open the door to SQL injection vulnerabilities.

Ways to Set Item Values in Oracle APEX

There are several ways to set the value of an item depending on how and where it's needed in your application.

1. Set Value Using Source Attribute

• In Page Designer, click on the item name.

• Scroll to the Source section.

• Choose the source type (Static Value, SQL Query, PL/SQL Function, etc.) and enter the value logic.

2. Set Value in PL/SQL Code

Inside a PL/SQL process or region, you can assign a value directly to an item like this:

BEGIN
  :MY_ITEM := 'new value';
END;

3. Set Value from a URL

You can pass a value to a page item using the f?p URL syntax:

f?p=100:101:1234567890::NO::MY_ITEM:ABC

In this case, the item MY_ITEM will be set to the value 'ABC'.

4. Set Value Using a Computation

Page computations are a built-in way to assign values at different processing points.

Example:

TO_CHAR(SYSDATE, 'Day DD Month, YYYY')

This might be used to assign the current date as a readable string.

5. Set Value Programmatically Using APEX PL/SQL API

For cases where you need to assign values from outside the page or session context, use the APEX_SESSION or APEX_UTIL API:

Example:

APEX_SESSION.SET_ITEM('MY_ITEM', 'new value');

Best Practices

• Use :ITEM_NAME for most SQL and PL/SQL references inside APEX.

• Avoid V() and NV() in SQL contexts to prevent performance issues.

• Do not use &ITEM. in PL/SQL or SQL to avoid security risks.

• When possible, rely on computations or source attributes to keep your logic clean and declarative.

• Always validate input if you're setting values based on user data or URLs.

Official Oracle APEX Documentation

To learn more, visit:
Referencing Items and Using Bind Variables – Oracle APEX Docs

Conclusion

Understanding how to reference and set item values correctly is critical to building dynamic, secure, and efficient applications in Oracle APEX. By using the appropriate syntax in the right context and following best practices, you ensure your application behaves predictably and performs well. Always refer to the Oracle documentation for the latest features and examples.