Using APEX_UTIL.STRING_TO_TABLE to Convert Selected Values in Oracle APEX

 

In Oracle APEX, it's common to work with multiple values selected by a user from a form element such as a multi-select list, checkbox group, or shuttle item. These components typically return a comma-separated string of values. To effectively process these selections in PL/SQL—for example, to loop through them or use them in SQL queries—you need to convert the string into a PL/SQL collection. Oracle APEX provides the APEX_UTIL.STRING_TO_TABLE function specifically for this purpose. This built-in utility function simplifies the handling of delimited strings by turning them into a PL/SQL table (array) that can be easily looped through or used in queries.

Using APEX_UTIL.STRING_TO_TABLE in Detail

The APEX_UTIL.STRING_TO_TABLE function converts a delimited string into a PL/SQL table of VARCHAR2 values. This is extremely useful when you need to iterate through selected values or use them in IN clauses within dynamic SQL or PL/SQL blocks.

Syntax

APEX_UTIL.STRING_TO_TABLE (
    p_string   IN  VARCHAR2,
    p_separator IN  VARCHAR2 DEFAULT ','
) RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;

• p_string: The string of values you want to convert (usually comma-separated).

• p_separator: The delimiter used in the string. Default is a comma.

Detailed Example 1: Loop Through Selected Values

Suppose you have a checkbox group item P1_DEPARTMENTS that returns a value like '10,20,30'.

DECLARE
  l_dept_ids APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_dept_ids := APEX_UTIL.STRING_TO_TABLE(:P1_DEPARTMENTS);
  
  FOR i IN 1 .. l_dept_ids.COUNT LOOP
    -- Process each department ID
    DBMS_OUTPUT.PUT_LINE('Selected Department ID: ' || l_dept_ids(i));
  END LOOP;
END;

This block reads the selected department IDs and prints them individually.

Detailed Example 2: Using in Dynamic SQL

DECLARE
  l_dept_ids APEX_APPLICATION_GLOBAL.VC_ARR2;
  l_sql      VARCHAR2(4000);
BEGIN
  l_dept_ids := APEX_UTIL.STRING_TO_TABLE(:P1_DEPARTMENTS);

  l_sql := 'SELECT * FROM employees WHERE department_id IN (';
  
  FOR i IN 1 .. l_dept_ids.COUNT LOOP
    IF i > 1 THEN
      l_sql := l_sql || ', ';
    END IF;
    l_sql := l_sql || l_dept_ids(i);
  END LOOP;

  l_sql := l_sql || ')';

  EXECUTE IMMEDIATE l_sql;
END;

This example dynamically builds and executes a SQL statement that filters employees based on selected department IDs.

Best Practices

• Always validate or sanitize user input before using it in dynamic SQL to prevent SQL injection.

• Use bind variables in dynamic SQL when possible for better performance and security.

• Avoid hardcoding delimiters—use the default comma unless your data specifically uses another delimiter.

• When working with numeric values, make sure they are converted properly to avoid data type mismatches.

• Use logging or debugging outputs (DBMS_OUTPUT) while developing to ensure your values are being parsed correctly.

Oracle APEX Documentation Link

For the official reference on APEX_UTIL.STRING_TO_TABLE, visit:
https://docs.oracle.com/en/database/oracle/apex/24.1/aeapi/APEX_UTIL.html#GUID-7E172D82-63ED-4A5E-9A3C-071DF00F6B34

Conclusion

The APEX_UTIL.STRING_TO_TABLE function is a powerful utility in Oracle APEX that bridges the gap between front-end item selections and backend PL/SQL logic. It enables developers to efficiently handle multiple selected values and use them in loops, validations, or dynamic SQL queries. By incorporating this function into your APEX development workflow, you can streamline your processing of multi-valued items and make your applications more dynamic and responsive. Always test thoroughly and leverage APEX’s rich built-in functionality to write clean, secure, and efficient code.