Tags
comma seperated string, comma to table, Insert Comma seperated string values into Table, split string, string to 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> 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, '(^|,)', '\1x'), TABLEN => L_COUNT, TAB => L_ARRAY);
7 DBMS_OUTPUT.PUT_LINE(L_COUNT);
8 FOR I IN 1 .. L_COUNT
9 LOOP
10 DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) || ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
11 INSERT INTO TEST VALUES
12 (SUBSTR(L_ARRAY(I), 2)
13 );
14 COMMIT;
15 END LOOP;
16 END;
17 /
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!
Lalit Kumar B 
Good One !! really saved my time…Tks
Thanks.
Love this!! What if I have comma delimited string similiar to this
L_INPUT VARCHAR2(4000) := ‘522,333,125,658,25,12-500’;
I receive the following Oracle error: ORA-20001: comma-separated list invalid near 12-50
How do I handle data similiar to the above?
thanks
That is by design. You can do it in other ways, see https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/
Hi Lalit, – I have seen some of your posts (on your blog and on SO) and I learned from them. Often your advice is top-notch. However, in this case I have to disagree.
DBMS_UTILITY.COMMA_TO_TABLE was created to be used when copying/recreating databases and schemas and such; it was not intended to be used to “split comma separated strings” as you are showing here. And it shouldn’t be used that way. If your input string contains “illegal” characters like dash ( – ) or double-quote ( ” ) or the at-sign ( @ ), the function will throw an error. Worse, it is not clear that the function will always throw an error; I haven’t found any examples, but in principle it is possible that some tokens in the input string will not throw an error, but they will simply be ignored, or that they will be accepted (and saved in the table) but with some meta-characters removed.
The bottom line is that this function wasn’t intended as a general-purpose string splitter, and it shouldn’t be used that way (as demonstrated by the examples I gave). At the very least, a warning about such issues should be given when you suggest using the function in this manner.
Hi,
As I said, this is not something new, Tom Kyte has already demonstrated it lot of times in AskTom. I agree it will throw error for illegal characters, and that is why it has limited use just like the example demonstrated above. If a developer wants to use it then they must test it and know the implications and also read my disclaimer 🙂
Hi Lalit,
Thanks for the tips given here.
Can you also please share the link where Tom Kyte has demonstrated this?
No better explanation than this https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1415803954123
Hi Lalit,
I have a list of numbers –
v_list := (1, 2, 3, 4, 5, …………….) — upto a 1000 numbers
I want to insert these numbers into a GTT because having >= 1000 values in a IN clause gives me error :
ora-01795 maximum number of expressions in a list is 1000
Can you please suggest how do i do this?
Thank you.
Regards,
Durga
Just create a lookup table with all the numbers you want in the list. Then use it as inner query.
Or, a simple workaround is to use an OR condition to split single IN condition to multiple IN conditions.
Or, use:
Thanks for responding Lalit.
I tried the 3 rd option of using the sys.odcinumberlist function, however i either get error
ora-00939 too many arguments or
invalid SQL.
This is what i am trying to do:
example:
select * from employees
where emp_id in (select * from table(sys.odcinumberlist(1,2,3,4 ….1020 values)));
I also searched a lot on google regaring this function, but could not find any information useful or relevant to a first time user like me.
Can you please guide me here?
P.S -> i am using oracle DB 12c enterprise edition release 12.1.0.2.0 – 64bit production
Thank you.
Regards,
Durga
Just to restate, my IN statement will be dynamically created in my actual code and it may have more than 1050 values in it.
We want to handle these values without getting error -:
ora-01795 maximum number of expressions in a list is 1000
Does sys.odcinumberlist also have a limitation on the number of values it can handle?
Please give me pointers for a better explanation of sys.odcinumberlist.
Best Regards,
Durga
Limit:
As I already mentioned in above example, sys.odcinumberlist it has limit of 32767 bytes/characters.
Usage example:
SELECT empno, ename FROM scott.emp WHERE empno IN ( SELECT column_value AS empno FROM TABLE(sys.odcinumberlist(7369,7499,7521)) ); EMPNO ENAME ---------- ---------- 7521 WARD 7499 ALLEN 7369 SMITH