Oracle – Insert Comma separated string values into Table

This is a small demonstartion of how to insert the values of a comma separated string to a table as different rows. This is not something new, Tom Kyte has already demonstrated it lot of times in AskTom. However, I just want to keep it simple, just to insert values of a comma separated string into a table.

Let’s see the demo :


SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DROP TABLE TEST
2 /

Table dropped
SQL> CREATE TABLE TEST (COL1 VARCHAR2(10))
2 /

Table created
SQL> DECLARE
2 L_INPUT VARCHAR2(4000) := 'THIS,IS,A,COMMA,SEPARATED,STRING';
3 L_COUNT BINARY_INTEGER;
4 L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
5 BEGIN
6 DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(L_INPUT,
7 '(^|,)',
8 '\1x'),
9 TABLEN => L_COUNT,
10 TAB => L_ARRAY);
11 DBMS_OUTPUT.PUT_LINE(L_COUNT);
12 FOR I IN 1 .. L_COUNT LOOP
13 DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) ||
14 ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
15 INSERT INTO TEST VALUES (SUBSTR(L_ARRAY(I), 2));
16 COMMIT;
17 END LOOP;
18 END;
19 /

6
Element 1 of array contains: THIS
Element 2 of array contains: IS
Element 3 of array contains: A
Element 4 of array contains: COMMA
Element 5 of array contains: SEPARATED
Element 6 of array contains: STRING

PL/SQL procedure successfully completed
SQL> SELECT * FROM TEST
2 /

COL1

THIS
IS
A
COMMA
SEPARATED
STRING

6 rows selected

SQL>

Hope it helps!