Referencing Data Values in Pages

While working on your pages, you'll often need to reference the values of page items or report columns for formatting, in queries, and in business logic.

• Using Data Values in HTML Expressions
  When fine-tuning data formatting, to reference the value of page item P1_NAME use &P1_NAME. starting with an ampersand and ending with a dot. This also works to reference application items, built-in substitution strings, and column values in HTML expressions for regions like Cards, Content Row, Template Components, and Interactive Grid.
• Using Data Values in Template Directives
  Use Template Directives in your HTML expressions to conditionally format data or iterate over a list of delimited values.
• Directives Reference in Your Dock
  The Universal Theme reference app contains examples of each template directive.
• Using the Context-Sensitive Help Tab
  The context-sensitive Help tab in Page Designer is a handy resource. It's in the same tab group as the Layout tab by default.
• 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.

When fine-tuning data formatting, to reference the value of page item P1_NAME use &P1_NAME. starting with an ampersand and ending with a dot. This also works to reference application items, built-in substitution strings, and column values in HTML expressions for regions like Cards, Content Row, Template Components, and Interactive Grid.

For example, consider a content row region based on the DEPT table where you need to reference the DNAME and LOC column values in the HTML expression for the Overline slot. You can use:

<strong>&DNAME.</strong> → &LOC.

In contrast, when working with columns in a Classic Report or Interactive Report, or when defining your own templates, use #NAME# instead. For example, imagine a classic report region based on the DEPT table where you want to format the department name column in a custom way. You can use the value of both DNAME and LOC columns in the DNAME column's HTML Expression as follows. If you also set the LOC column type to be Hidden then it won't occupy an additional column.

<strong>#DNAME#</strong> → #LOC#

When using a page item or region source's column value in an HTML attribute, use the additional !ATTR qualifier as shown below to ensure the page formats the value correctly to be used for that purpose:

<a href="https://docs.oracle.com/en/database/oracle/apex/26.1/apxdc/using-data-values-html-expressions.html#TARGET_LINK!ATTR#"><strong>#DNAME#</strong></a>

If the column value itself includes HTML markup, use the !RAW qualifier to avoid the default escaping of characters like <, >, and & that have special meaning in HTML. For example, if a DESCRIPTION_IN_HTML column contains HTML markup, then reference it like this to ensure the browser sees the HTML markup as intended:

<i>#DESCRIPTION_IN_HTML!RAW#</i>

These qualifiers also work for &NAME!RAW. and &NAME!ATTR. use cases.

Use Template Directives in your HTML expressions to conditionally format data or iterate over a list of delimited values.

When referencing data values inside the curly braces of a directive, use its name with no prefix or suffix.

Depending on the context, what you put inside the directive uses #NAME# or &NAME. notation. For example, in a Cards, Content Row, Template Components, and Interactive Grid region, use the &NAME. substitution syntax as follows to include the value of TITLE if it is not empty.

{if TITLE/}&TITLE.{endif/}

In a Classic or Interactive Report, or when defining your own templates, use &NAME. for page items, application items, or built-in substitution strings, but #COLUMNNAME# notation for column names. Also note that space before the closing /} is allowed.

{if TITLE /}#TITLE#{endif/}

Template directives make it easy to combine columns into a formatted result with appropriate conditional formatting. Imagine a PERSONS table with columns for HONORIFIC, FIRST_NAME, and LAST_NAME. In an email template, to format a greeting as "Dear Professor Smith," if an honorific is present and "Dear Jennifer," otherwise, you can use template directives like this:

Dear {if HONORIFIC/}#HONORIFIC# #LAST_NAME#{else/}#FIRST_NAME#{endif/}, 

In an Interactive Report based on an ACTIVITIES tables, suppose a TAGS column contains a colon-delimited list of tag names. To format each tag as a chip, you can use the {loop/} directive in the column's HTML expression like this:

{loop ":" TAGS/}<span class="a-Chip">&APEX$ITEM.</span>{endloop/}

This produces the output you see below in the Tags column. The text of each tag appears inside a separate rounded rectangle in each row of the report.

APEX offers the template directives listed below. For more information, see Using Template Directives in Oracle APEX App Builder User’s Guide.

{if NAME/} …
{else/} …
{endif/}

{case NAME/}
  {when X/} …
  {when Y/} …
  {otherwise/} …
{endcase/}

{loop "," NAME/}
  … &APEX$ITEM. … &APEX$I. …
{endloop/}

{with/}
   ATTR1:=value1
   ATTR2:=#NAME#
{apply TEMPLATENAME/}

The Universal Theme reference app contains examples of each template directive.

Visit and bookmark the Universal Theme reference app at oracleapex.com/ut. It supports Progressive Web App installation, so you can launch it anytime like a native app from your Dock or TaskBar. As shown below, the Template Directives page in its Reference section has examples of each template directive along with additional information about supported options.

The context-sensitive Help tab in Page Designer is a handy resource. It's in the same tab group as the Layout tab by default.

For any property you select in the Property Editor, the Help tab shows you the substitutions the property supports and whether it allows Template Directives. Some entries also have helpful examples as shown below. In other editing pages in App Builder, click the help icon next to any field for similar information.

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.

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.

Some common examples include using bind variables in:

• region SQL queries
• WHERE clauses
• local and shared component list of values
• validations
• page processes and workflow activities
• server-side condition expressions, and
• dynamic actions of type Execute Server-side Code.

Outside of data definition language (DDL) statements, anywhere SQL or PL/SQL allows an expression, you can use a bind variable instead. In PL/SQL, to change the value of a page item or application item use := to assign a value to a :NAME bind variable. For example, if business logic in page 5 needs to change the value of the P5_JOB page item to ANALYST if it currently has the value CLERK, then the PL/SQL code looks like this:

-- Change clerk to analyst
IF :P5_JOB = 'CLERK' THEN
   :P5_JOB := 'ANALYST';
END IF;

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.

This includes grid-related usages like:

• validations for a grid column
• page processes with their Editable Region property set to a grid, and
• dynamic actions of type Execute Server-side Code reacting to grid column value change.

In the dynamic action case, remember to mention the column names you're referencing in the Items to Submit and Items to Return properties. Those values are a comma-separated list of one or more column names without the preceding colon in the name.

In the page processing use case for new or modified grid rows, in addition to being able to change the value of a page item or application item, you can use a bind variable to set the value of a column by assigning an expression to a :COLUMNNAME bind variable using := as shown below:

-- Change clerk to analyst in current grid row being processed
IF :JOB = 'CLERK' THEN
   :JOB := 'ANALYST';
END IF;

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.

Even when you configure a page item like P5_START_DATE as a Date Picker or define a P5_MAX_QUANTITY item as a Number Field, APEX binds your references to :P5_START_DATE and :P5_MAX_QUANTITY as VARCHAR2 string values. There are two exceptions to this rule. You can optionally configure the data type to be:

• CLOB for Hidden, Text Area, and Rich Text Editor page items
• BOOLEAN for Switch and Checkbox page items.

• Working with Data Values as Numbers
  If a page item contains a numeric value, using its value as a VARCHAR2-typed bind variable is fine when the database performs automatic type conversion.
• Working with Data Values as Dates
  If a page item contains a date value, then using its value as a VARCHAR2-typed bind variable can raise an error if the page item uses a different format mask than the application default.
• Working with Delimited Values
  When a page item contains multiple value separated by a delimiter like colon (:) or comma (,) you can use its multiple values in SQL or PL/SQL.

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.

In cases where that's not a priority, use the GET_TYPE() routines in the APEX_SESSION_STATE package to access the value of page items, application items, or built-in substitution string names. To work with data values as strings in your function and procedure code, the V() function is identical to apex_session_state.get_varchar2() and fewer characters to type.

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.

If you reference multiple page items as bind variables, list each name separated by commas in the value of this property. While you precede the page item name with a colon when using it as a bind variable, for this region property, page item names without the colon. Failure to remember this step can result in an application malfunction. When you forget to include a page item's name, its bind variable inadvertently evaluates to null. This can cause a query to return no rows, or business logic to take the wrong execution path.

Similarly, when referencing a page item or grid column value as a bind variable in a Dynamic Action of type Execute Server-side Code, ensure you set the Items to Submit and Items to Return properties appropriately. Forgetting to do this can produce No Data Found errors or make your page appear to function incorrectly when server-side computed values are not returned to the page as expected.

If a page item contains a numeric value, using its value as a VARCHAR2-typed bind variable is fine when the database performs automatic type conversion.

For example, in a WHERE clause like the following, the database implicitly converts the string value of :P5_MAX_QUANTITY to a number to compare it with a number column like QUANTITY:

QUANTITY BETWEEN 1 AND :P5_MAX_QUANTITY

QUANTITY BETWEEN 1 AND CASE CATEGORY
                          WHEN 'SMALL' THEN 10
                          WHEN 'LARGE' THEN :P5_MAX_QUANTITY
                          ELSE              15
                       END 

ORA-00932: expression (:1) is of data type CHAR,
           which is incompatible with expected data type NUMBER

The solution is to reference the value as a number using the GET_NUMBER() function in the APEX_SESSION_STATE package. This automatically accounts for any format mask you might have configured on the page item to correctly convert the value to a number result.

Another common situation is comparing the value of two page items containing numbers. Consider the following query by example page featuring a P1_SAL_RANGE_LOW and P1_SAL_RANGE_HIGH page items of type Number Field.

If you define a validator Check Salary Range with the PL/SQL expression below, the user submitting a low value of 22 and a high value of 3 does not receive a validation error as expected. This happens because the string values containing digits 22 and 3 get compared in text order rather than numeric order.

:P1_SAL_RANGE_LOW <= :P1_SAL_RANGE_HIGH

To have the values compared as numbers, rewrite the validation rule as follows. Notice the bind variable notation is replaced by passing the name of the page item to the GET_NUMBER() function.

apex_session_state.get_number('P1_SAL_RANGE_LOW')
 <= apex_session_state.get_number('P1_SAL_RANGE_HIGH')

With this solution in place, clicking Find Employees now produces the expected validation error as shown below, "Salary range low value must be less than or equal to high value."

If a page item contains a date value, then using its value as a VARCHAR2-typed bind variable can raise an error if the page item uses a different format mask than the application default.

For example, consider the following where clause using the value of a Date Picker page item as a bind variable:

HIREDATE < :P34_HIRED_BEFORE

ORA-01858: A non-numeric character was found instead of a numeric character.

The solution is to reference the value as a date using the GET_TIMESTAMP() function in the APEX_SESSION_STATE package like this. Notice the bind variable notation is replaced by passing the name of the page item to the GET_TIMESTAMP() function.

HIREDATE < apex_session_state.get_timestamp('P34_HIRED_BEFORE')

This automatically accounts for any format mask you might have configured on the page item to correctly convert the value to a date result. Forgetting this can also lead to unexpected results, especially when comparing date values.

Consider the following query by example page featuring a P1_HIREDATE_RANGE_START and P1_HIREDATE_RANGE_END page items of type Date Picker.

:P1_HIREDATE_RANGE_START <= :P1_HIREDATE_RANGE_END

apex_session_state.get_timestamp('P1_HIREDATE_RANGE_START')
 <= apex_session_state.get_timestamp('P1_HIREDATE_RANGE_END')

With this solution in place, clicking Find Employees now produces the expected validation error as shown below, "Hiredate Range ends before it starts."

When a page item contains multiple value separated by a delimiter like colon (:) or comma (,) you can use its multiple values in SQL or PL/SQL.

• Working with Delimited Number Values
  To work with delimited number values in SQL or PL/SQL, use the apex_string.split_numbers() function.
• Working with Delimited String Values
  To work delimited values in SQL or PL/SQL, use the apex_string.split() function.

To work with delimited number values in SQL or PL/SQL, use the apex_string.split_numbers() function.

Suppose P5_SELECTED_EMPNOS is a Select Many page item with colon (:) as delimiter. If you configure its list of values using a query with ENAME as the display value and EMPNO as the return value, then after the user selects multiple employees the value of P5_SELECTED_EMPNOS might look like the string "7839:7369:7654".

select empno, ename, sal
  from emp
 where empno in (:P5_SELECTED_EMPNOS) /* Tempting, but problematic */

ORA-01722: unable to convert string value containing ':' to a number:
ORA-03302: (ORA-01722 details) invalid string value: 7839:7369:7654

The solution involves splitting the string into its separate values using the SPLIT_NUMBERS() function in the APEX_STRING package. Then you can select the COLUMN_VALUE from its single-column table result. Notice the example below passes the colon (':') as the second argument to indicate the value delimiter in use:

select empno, ename, sal
  from emp
 where empno in (select column_value
                   from apex_string.split_numbers(:P5_SELECTED_EMPNOS,':'))

declare
   l_selected_empnos apex_t_number;
begin
   l_selected_empnos := apex_string.split_numbers(:P5_SELECTED_EMPNOS,':');
   for j in 1..l_selected_empnos.count loop
      -- Use the j-th employee number here
      -- l_selected_empnos(j)
   end loop;
end;

To work delimited values in SQL or PL/SQL, use the apex_string.split() function.

Consider a Select Many page item named P5_SELECTED_SIZES that uses the vertical bar (|) as delimiter. If you configured its list of values using a query with SIZE_DESCRIPTION as the display value and SIZE_CODE as the return value, then after the user selects multiple sizes, the value of P5_SELECTED_SIZES might look like the string "SM|MD|XXL".

select id, sku, price
  from items
 where item_size in (:P5_SELECTED_SIZES) /* Tempting, but problematic */

However, attempting this when there are multiple delimited values in the page item returns no rows because this syntax only matches an item row with an ITEM_SIZE value of the literal value "SM|MD|XXL".

select id, sku, price
  from items
 where item_size in (select column_value
                       from apex_string.split(:P5_SELECTED_SIZES,'|'))

declare
   l_selected_sizes apex_t_varchar2;
begin
   l_selected_sizes := apex_string.split(:P5_SELECTED_SIZES,'|');
   for j in 1..l_selected_sizes.count loop
      -- Use the j-th size code here
      -- l_selected_sizes(j)
   end loop;
end;