Re: inserting comma-separated values with pl/sql

Christoph.Seidel1_at_gmx.de said...
> Hi there,
>
> i have a stored proc which has a varchar parameter which contains a list of
> values, separated by comma.
>
> e.g.: 123,456,789
>
> what i wanna do is to separate the values and insert them into a table
>
> e.g.
>
> insert into t (f) values (123);
> insert into t (f) values (456);
> insert into t (f) values (789);
>
> Can this be done with pl/sql? And if i can be done, how?
>

PL/SQL has just what you're looking for ... FOR loops and the INSTR / SUBSTR functions.

Def: FOR <var> IN <start>..<end> LOOP

       ...statements...
     END LOOP;

Def: X := INSTR(<string>, <pattern> [,<start>] [,<nth>]);

Def: X := SUBSTR(<string>, <start> [,<length>]);

Def: X := LENGTH(<string>);

-- 
/Karsten
DBA > retired > DBA