converting comma separated values into rows in oracle

[mahe23]

All,

How do One convert a comma separated column from a text file into rows in oracle. I have a scenario where the list of comma separated values changes dynamically.

It is like:

abc, ttt, 1,2,34,56,67,,

afg, ttt, 3,4,5,6,,,,,

try, ttt, 4,5,,

I need to load this data into a oracle table which has columns like this:


name time value_abc value_afg value_try

abc ttt 1

abc ttt 2......



afg ttt 3

afg ttt 4.....



try ttt 4

try ttt 5 and so on...

Thanks a bunch...

[masdi2t]

All,

How do One convert a comma separated column from a text file into rows in oracle. I have a scenario where the list of comma separated values changes dynamically.

It is like:

abc, ttt, 1,2,34,56,67,,

afg, ttt, 3,4,5,6,,,,,

try, ttt, 4,5,,

I need to load this data into a oracle table which has columns like this:


name time value_abc value_afg value_try

abc ttt 1

abc ttt 2......



afg ttt 3

afg ttt 4.....



try ttt 4

try ttt 5 and so on...

Thanks a bunch...

you can use comma_to_table procedure from DBMS_UTILITY package.
i give you little ex.

i've a little table with 2 column, create table dest(x int, y char(1));
n this is d script:

DECLARE
plist VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
ptablen BINARY_INTEGER;
ptab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('plist : ' || plist);
DBMS_UTILITY.comma_to_table (
list => plist,
tablen => ptablen,
tab => ptab);
FOR i IN 1 .. ptablen LOOP
INSERT INTO dest VALUES (i, ptab(i));
END LOOP;
END;

[Medhatithi]

you can use comma_to_table procedure from DBMS_UTILITY package.
i give you little ex.

i've a little table with 2 column, create table dest(x int, y char(1));
n this is d script:

DECLARE
plist VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
ptablen BINARY_INTEGER;
ptab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('plist : ' || plist);
DBMS_UTILITY.comma_to_table (
list => plist,
tablen => ptablen,
tab => ptab);
FOR i IN 1 .. ptablen LOOP
INSERT INTO dest VALUES (i, ptab(i));
END LOOP;
END;

There are however, certain limitations for using this package. You must go through them in Oracle documentation to use it. Alternatively, you can use SUBSTR & INSTR to separate the values. I can give you a sample code:


declare
lc NUMBER(10):=1;
v_loanlen NUMBER(10);
v_len NUMBER(10);
v_start_sub NUMBER(10):=1;
v_end_sub NUMBER(10);
begin
v_loanlen:=length(p_loans);
LOOP
EXIT WHEN v_end_sub=v_loanlen;
v_end_sub:=INSTR(p_loans,',',1,lc)-1;
IF v_end_sub<0
THEN
v_end_sub:=v_loanlen;
END IF;
v_len:=v_end_sub-v_start_sub+1;
insert into table_name(column_name)
values(SUBSTR(p_loans,v_start_sub,v_len));
lc:=lc+1;
v_start_sub:=v_end_sub+2;
END LOOP;

[dchamber]

Here is a way to do it with straight SQL in Oracle. This uses the CSV string a,b,c,d ; However, it could be any string.

select substr(csv, 
   instr(csv,',',1,lev) + 1, 
   instr(csv,',',1,lev+1 )-instr(csv,',',1,lev)-1) 
from 
  (select ','||'a,b,c,d'||',' csv from dual), 
  (select level lev from dual connect by level <= 100) 
where lev <= length(csv)-length(replace(csv,','))-1;