Tuesday, 9 December 2008

Convert comma separated list into an array - dbms_utility.comma_to_table

Simple way to convert a comma separated list held in a single variable into its component parts.
pl/sql equivalent to php explode
e.g.

SET SERVEROUTPUT ON
DECLARE
l_list1 VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
l_list2 VARCHAR2(50);
l_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

DBMS_UTILITY.comma_to_table (
list => l_list1,
tablen => l_tablen,
tab => l_tab);

FOR i IN 1 .. l_tablen LOOP
DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;

DBMS_UTILITY.table_to_comma (
tab => l_tab,
tablen => l_tablen,
list => l_list2);

DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);



END;
http://www.oracle-base.com/articles/9i/UsefulProceduresAndFunctions9i.php

3 comments:

--PK. said...

Hi,

I tried with an UID list '100,200,101,5' and I got the following error: ORA-00931: missing identifier

How would you do with this list ?

Thanks in advance,

--P

wojtekz_ said...

Read the Oracle doc first: These procedures converts a comma-delimited list of names into a PL/SQL table of [Oracle] names (...) out tab - PL/SQL table which contains list of table names.
It don't support any lists, only Oracle names lists.

Regards

wojtekz_ said...

ups... it can be any list, but table can be only in two types:
TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER or
TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER

Regards