About Bind Variables

You can use bind variables within an application process or SQL query to reference session state of a specified item. For example:

SELECT * FROM emp WHERE name like '%' || :SEARCH_STRING || '%'

In this example, the search string is a page item. If the region type is defined as SQL Query, you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.

When using bind variable syntax, remember the following rules:

Bind variable names must correspond to an item name.
Bind variable names are not case-sensitive.
Bind variable names cannot be longer than 30 characters (that is, they must be a valid Oracle identifier).

Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variable syntax, the item name must be 30 characters or less.

Using Bind Variables in Regions Based on a SQL Query or LOV

If your region type is defined as a SQL Query, SQL Query (plsql function body returning SQL query), or list of values (LOV), you can reference session state using the following syntax:

:MY_ITEM

One common way to do this is to incorporate a session state variable in a WHERE clause. The following example shows how to bind the value of the item THE_DEPTNO into a region defined from a SQL Query.

SELECT ename, job, sal
FROM emp
WHERE deptno = :THE_DEPTNO

Using Bind Variables in PL/SQL Procedures

For region types defined as a PL/SQL Procedure, regions are constructed using PL/SQL anonymous block syntax. In other words, the beginning and ending are added automatically around the PL/SQL. For example:

INSERT INTO emp (empno, ename, job) 
VALUES (:P1_empno, :P1_name, :P1_job);

In this example, the values of the empno, ename, and job are populated by the values of P1_empno, P1_name, and P1_job.