4.5 Using Data Values in SQL and PL/SQL

Depending on the context, in your SQL and PL/SQL you use either :NAME bind variable syntax or GET_TYPE() functions in the APEX_SESSION_STATE package to use data values by name.

These include the values of page items, application items, and the names of built-in substitution strings like APP_USER, APP_ID, APP_PAGE_ID, and many others. For more information on the predefined names you can reference, see Using Available Built-in Substitution Strings in Oracle APEX App Builder User’s Guide

• Working with Page and App Data Values
  In the SQL and the PL/SQL you write directly in APEX pages and component definitions, use :NAME bind variable syntax to reference the data value of a page item or application item by name.
• Working with Column Data Values
  When your SQL or PL/SQL processes rows for an Interactive Grid, in addition to page and application items you can also reference a column value in the current row using a :COLUMNAME bind variable.
• Values Bind as Strings
  Bind variables in your SQL and PL/SQL are always treated as strings, except when using database 26ai and explicitly setting a page item's data type to BOOLEAN.
• Using Data Values in Named Program Units
  In named program units like functions and procedures, to make code more reusable, you can accept values the business logic depends on as input parameters. Then pages invoking the function or procedure can pass in appropriate values by referencing page items.
• Configure Items to Submit and Return
  Whenever you reference a page item value as a bind variable in a region data source query or WHERE clause, make sure to list its name in the region's Page Items to Submit property.