Oracle: Breaking Comma Separated String Using DBMS_UTILITY.COMMA_TO_TABLE

This is really good topic for split csv data:

original link : http://www.techiegyan.com/?p=439

There are times one has to break a comma separated string and extract actual values from it. Possible reasons for this requirement could be when a procedure takes an unknown number of strings as an input (although arrays/collections can be used instead, but right now we’ll stick to a single string) or while reading csv file etc.

Oracle provides a DBMS_UTILITY procedure called COMMA_TO_TABLE. This procedure assumes a comma delimiter, if the delimiter is something else, a REPLACE would need to be done beforehand.

Following code snippet displays the use of COMMA_TO_TABLE:

Sample Code:

DECLARE
 lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta';
 lb_cnt             BINARY_INTEGER;
 la_Tab_Str       DBMS_UTILITY.UNCL_ARRAY;
BEGIN
 -- parse the string into comma separated table
 DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List, lb_cnt, la_Tab_Str);
 FOR i IN 1 .. la_Tab_Str.COUNT LOOP
   -- display substring
   DBMS_OUTPUT.PUT_LINE(TRIM(la_Tab_Str(i)));
 END LOOP;
END;
/
 

Output:
Alpha
Beta
Gama
Delta

Although quite useful, this procedure has it’s own restrictions as it was written primarily for use within replication internally by Oracle, and parse IDENTIFIERS rather than strings. Refer “*IMPORTANT CAVEAT*” in http://www.oratechinfo.co.uk/delimited_lists_to_collections.html#dbms_utility. Due to this restriction, names starting with numbers or including special characters are not supported.
e.g. if the above snippet had :

lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, 123number';
 

or

lv_Str_List       VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, specialchar#@!@&**';
 

The following error would be returned:
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at “SYS.DBMS_UTILITY”, line 125
ORA-06512: at “SYS.DBMS_UTILITY”, line 160
ORA-06512: at “SYS.DBMS_UTILITY”, line 202
ORA-06512: at line 8

However, a workaround can be used for the procedure to support this. If the not-supported substrings are included in double quotes, they can be parsed by this procedure. The easier way to implement this is to put all the substrings in the comma separated strings within double quotes. Following code snippet shows how:

Sample Code:

DECLARE
 lv_Str_List          VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, 123number';
 lb_cnt                BINARY_INTEGER;
 la_Tab_Str          DBMS_UTILITY.UNCL_ARRAY;
 lv_Str_List_Quote VARCHAR2(3000);
BEGIN
 -- put all the strings in double quotes to avoid special character problems 
 -- while parsing with comma_to_table procedure
 lv_Str_List_Quote := '"' || REPLACE( lv_Str_List, ',', '","' ) || '"';

 -- parse the string into comma separated table
 DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List_Quote, lb_cnt, la_Tab_Str);

 FOR i IN 1 .. la_Tab_Str.COUNT LOOP
   -- remove double quotes added earlier and trim to fetch the actual string
   DBMS_OUTPUT.PUT_LINE(TRIM(REPLACE( la_Tab_Str(i), '"', '' )));
 END LOOP;
END;
/
 

Output:
Alpha
Beta
Gama
Delta
123number

In the above snippet, the comma separated string is started and appended with ” (double quote) and all , (comma) are replaced by “,” (double quote comma double quote) so that each substring starts and ends with a double quote. Then, the strings are extracted into a table using COMMA_TO_TABLE. These strings still are within double quotes, so the for loop fetches each string and the double quote is replaced with nothing. Also, the final string is trimmed to clean any extra leading or trailing spaces.

Note: There is a TABLE_TO_COMMA which does the reverse.