Oracle: Breaking Comma Separated String Using DBMS_UTILITY.COMMA_TO_TABLE

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:

  1.  
  2. DECLARE
  3. lv_Str_List VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta';
  4. lb_cnt BINARY_INTEGER;
  5. la_Tab_Str DBMS_UTILITY.UNCL_ARRAY;
  6. BEGIN
  7. -- parse the string into comma separated table
  8. DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List, lb_cnt, la_Tab_Str);
  9. FOR i IN 1 .. la_Tab_Str.COUNT LOOP
  10. -- display substring
  11. DBMS_OUTPUT.PUT_LINE(TRIM(la_Tab_Str(i)));
  12. END LOOP;
  13. END;
  14. /
  15.  

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 :

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

or

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

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:

  1.  
  2. DECLARE
  3. lv_Str_List VARCHAR2(1000) := 'Alpha, Beta, Gama, Delta, 123number';
  4. lb_cnt BINARY_INTEGER;
  5. la_Tab_Str DBMS_UTILITY.UNCL_ARRAY;
  6. lv_Str_List_Quote VARCHAR2(3000);
  7. BEGIN
  8. -- put all the strings in double quotes to avoid special character problems
  9. -- while parsing with comma_to_table procedure
  10. lv_Str_List_Quote := '"' || REPLACE( lv_Str_List, ',', '","' ) || '"';
  11.  
  12. -- parse the string into comma separated table
  13. DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List_Quote, lb_cnt, la_Tab_Str);
  14.  
  15. FOR i IN 1 .. la_Tab_Str.COUNT LOOP
  16. -- remove double quotes added earlier and trim to fetch the actual string
  17. DBMS_OUTPUT.PUT_LINE(TRIM(REPLACE( la_Tab_Str(i), '"', '' )));
  18. END LOOP;
  19. END;
  20. /
  21.  

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.

One thought on “Oracle: Breaking Comma Separated String Using DBMS_UTILITY.COMMA_TO_TABLE

  1. By experience, some of us know that if we pass comma separated values as a string variable Eg:- ’101,103′ to the IN clause, it does not give the desired output as illustrated below -

    CREATE TABLE SUBJECT_TABLE(SUBJECT_ID VARCHAR2(10), SUBJECT_NAME VARCHAR2(25));

    INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’101′, ‘MATHEMATICS’);
    INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’102′, ‘ENGLISH’);
    INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’103′, ‘SCIENCE’);
    INSERT INTO SUBJECT_TABLE (SUBJECT_ID, SUBJECT_NAME) VALUES (’104′, ‘HISTORY’);
    COMMIT;

    An anonymous block as below will not Print any output –

    DECLARE
    P_SUBJECT_ID VARCHAR2(100) := NULL;
    CURSOR CUR_SUBNAME IS
    SELECT SUBJECT_NAME
    FROM SUBJECT_TABLE
    WHERE SUBJECT_ID IN (P_SUBJECT_ID);
    BEGIN
    P_SUBJECT_ID := ’101,103′;
    FOR CUR_SUBNAME_REC IN CUR_SUBNAME LOOP
    DBMS_OUTPUT.PUT_LINE(CUR_SUBNAME_REC.SUBJECT_NAME);
    END LOOP;
    END;
    /

    However, when we use “Regular expression & Connect by” clause to convert the Parameter string to column values and use in Cursor, we’ll get the desired output as explained below -

    DECLARE
    P_SUBJECT_ID VARCHAR2(100) := NULL;
    CURSOR CUR_SUBNAME IS
    SELECT SUBJECT_NAME
    FROM SUBJECT_TABLE
    WHERE SUBJECT_ID IN
    ((SELECT REGEXP_SUBSTR(P_SUBJECT_ID, ‘[^,]+’, 1, LEVEL)
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(P_SUBJECT_ID, ‘[^,]+’, 1, LEVEL) IS NOT NULL));
    BEGIN
    P_SUBJECT_ID := ’101,103′;
    FOR CUR_SUBNAME_REC IN CUR_SUBNAME LOOP
    DBMS_OUTPUT.PUT_LINE(CUR_SUBNAME_REC.SUBJECT_NAME);
    END LOOP;
    END;
    /

    This may be useful when parameter list is prepared by adding comma separated value(s) within different blocks and finally passed to a cursor or SQL statement for use within IN clause.

Leave a Reply