Re: inserting comma-separated values with pl/sql

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Sat, 22 Feb 2003 03:01:02 GMT
Message-ID: <Xns9329DF6D269C9pobox002bebubcom@204.127.199.17>

"Christoph Seidel" <Christoph.Seidel1_at_gmx.de> wrote in news:b35vdh$1j53eo$1_at_ID-143718.news.dfncis.de:

> 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?
>

Hello Cristoph

Yes it can be done, I use something like this along the lines Kenneth's suggestion. As per the points he raised it just inserts a null for double ',,'s, but you can tweak it to skip those if its a problem

SQL> declare
  2 l_str varchar2(20) := '123,456,789';   3 begin

  4      while l_str is not null loop
  5          insert into t values (
  6              to_number(rtrim(substr(
  7                  l_str,1,instr(l_str||',',',')),','))
  8              );
  9          l_str := substr(l_str,instr(l_str||',',',')+1);
 10      end loop;

 11 end;
 12 /

PL/SQL procedure successfully completed.

SQL> select * from t;

         N

---

       123
       456
       789

Hth

Martin Received on Fri Feb 21 2003 - 21:01:02 CST