Oracle DBMS_UTILITY.Comma_To_Table
-- Using DBMS_UTILITY.Comma_To_Table(list, tablen, tab) a 
-- comma separated string (list) can be converted to a 
-- PL/SQL table. This works for "," only as the name
-- implies.  Other separators such as ";", "|" etc. need
-- to be replaced to comma to use this procedure. 

SET SERVEROUTPUT ON;

DECLARE
 o_array     DBMS_UTILITY.uncl_array;
 o_count     BINARY_INTEGER;
 i_str       VARCHAR2(100) := 'sun,mon,tue,wed,thu,fri,sat';
 i_str2      VARCHAR2(100) := 'sun;mon|tue,wed,thu,fri,sat';

BEGIN
  DBMS_UTILITY.comma_to_table(list => i_str,
                              tablen => o_count,
                              tab => o_array
                             );

  FOR idx IN 1..o_count LOOP
   DBMS_OUTPUT.PUT_LINE('idx['||idx||'] -> '||o_array(idx)); 
  END LOOP;

  i_str2 := REPLACE(REPLACE(i_str2,';',','),'|',',');

  DBMS_UTILITY.comma_to_table(list => i_str2,
                              tablen => o_count,
                              tab => o_array
                             );

  FOR idx IN 1..o_count LOOP
   DBMS_OUTPUT.PUT_LINE('idx['||idx||'] -> '||o_array(idx)); 
  END LOOP;
END;
/

=> Comma_To_Table Output


Oracle registered trademark of Oracle Corporation.

Last Revised On: May 15th, 2015

  365