DBMS_UTILITY.COMMA_TO_TABLE splits a comma-separated list into a PL/SQL table, and TABLE_TO_COMMA joins one back into a list. They are built for lists of names, such as table or column names, which explains both what they do well and where they fail.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_utility.comma_to_table(list, count_out, table_out); -- table_out: dbms_utility.uncl_array dbms_utility.table_to_comma(table_in, count_out, list_out);
Split and Join a List
Example:
declare
v_list dbms_utility.uncl_array;
v_count binary_integer;
v_back varchar2(100);
begin
dbms_utility.comma_to_table('DXB,LHR,"São",SIN', v_count, v_list);
for i in 1 .. v_count loop
dbms_output.put_line(i || ': ' || v_list(i));
end loop;
dbms_utility.table_to_comma(v_list, v_count, v_back);
dbms_output.put_line('back: ' || v_back);
end;
/Output:
1: DXB 2: LHR 3: "São" 4: SIN back: DXB,LHR,"São",SIN PL/SQL procedure successfully completed.
The list splits into four items, and the quoted item keeps its quotes and its accented character. TABLE_TO_COMMA joins them back into the original list.
Only Names
Each item must be a valid Oracle name. Numbers and unquoted text with spaces are rejected.
Example:
-- each item must be a valid name: numbers are not
declare
v_list dbms_utility.uncl_array;
v_count binary_integer;
begin
dbms_utility.comma_to_table('10,20,30', v_count, v_list);
end;
/
-- and neither are names with spaces, unless quoted
declare
v_list dbms_utility.uncl_array;
v_count binary_integer;
begin
dbms_utility.comma_to_table('Dubai,New York', v_count, v_list);
end;
/Output:
declare * ERROR at line 1: ORA-20001: comma-separated list invalid near 0 ORA-06512: at "SYS.DBMS_UTILITY", line 226 ORA-06512: at "SYS.DBMS_UTILITY", line 364 ORA-06512: at line 5 declare * ERROR at line 1: ORA-20001: comma-separated list invalid near w Yor ORA-06512: at "SYS.DBMS_UTILITY", line 229 ORA-06512: at "SYS.DBMS_UTILITY", line 207 ORA-06512: at "SYS.DBMS_UTILITY", line 364 ORA-06512: at line 5
Both lists fail with ORA-20001. For general CSV values, split with REGEXP_SUBSTR or JSON_TABLE instead, and keep COMMA_TO_TABLE for lists of names.
Things to Know
- Items are returned as written: not uppercased, with spaces around them and double quotes kept.
- The count is the number of items, and the table also has an empty element after the last one.
- The table type is DBMS_UTILITY.UNCL_ARRAY, an associative array of VARCHAR2(227).
Related Guides
Conclusion
DBMS_UTILITY.COMMA_TO_TABLE and TABLE_TO_COMMA convert between comma-separated lists of names and PL/SQL tables. They reject numbers and other values that are not valid names, so use them for name lists and other splitting methods for general data.
