4.5.3.3.1 Working with Delimited Number Values

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".

An initial thought might be to use the bind variable as follows:

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

However, this technique fails when there are multiple delimited values in the page item. It would produce a runtime error like this:

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,':'))

To work with the multiple values in PL/SQL you can define a variable of type apex_t_number. This is APEX's built-in list of numbers type. Then you can call the same split_numbers() function:

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;