Re: inserting comma-separated values with pl/sql

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 22 Feb 2003 18:49:11 -0800
Message-ID: <45a06b65.0302221849.7f972b42@posting.google.com>

"Christoph Seidel" <Christoph.Seidel1_at_gmx.de> wrote in message news:<b38ked$1jq1ek$1_at_ID-143718.news.dfncis.de>...
> DA Morgan wrote:
> > Christoph Seidel wrote:
> >
> >> Christoph Seidel wrote:
> >>> DA Morgan wrote:
> >>>> Kenneth and Karsten's responses are good general methods. If your
> >>>> comma delimited values are no more than 30 characters,
> >>>> DBMS_UTILITY.COMMA_TO_TABLE is far more efficient.
> >>>
> >>> well, really great tip! thanx alot!
> >>
> >> oops, not a great tip :) does not work with numbers, i think with
> >> "list of names" the docs mean list of object names in the data
> >> dictionary!?
> >
> > Works with numbers ... you just need to use REPLACE to replace the
> > commas with "," (double quote comma double quote) and concatenate
> > double quotes at the beginning and end of the string.
>
> Hu, how can one find this out since the package is obfuscated (at least in
> my ora 9i)?!

I think you are out of luck with that approach, the list of names does need to consist of database objects so unless you've been doing something like loading java in the database you'll be unlikely to have an object named after every possible numeric combination.

The instr / substr method never seemed too slow, but I thought as it was being discussed I better check it out and see if it could handle more than 30 char while I was at it.

SQL> create table t (n number);

Table created.

SQL> declare

  2      l_str varchar2(32000) := '123,456,789,';
  3      l_str_val varchar2(100);
  4      l_start number;
  5  begin
  6      for i in 1..11 loop
  7          l_str := l_str || l_str;
  8      end loop;
  9      dbms_output.put_line('Num chars: ' ||
 10          to_char(length(l_str)));
 11      l_start := dbms_utility.get_time;
 12      while l_str is not null loop
 13          l_str_val :=
rtrim(substr(l_str,1,instr(l_str||',',',')),',');
 14          if l_str_val is not null then
 15              insert into t values (to_number(l_str_val));
 16          end if;
 17          l_str := substr(l_str,instr(l_str||',',',')+1);
 18      end loop;
 19      dbms_output.put_line('Run time: ' ||
 20          to_char(dbms_utility.get_time - l_start) ||
 21          ' hsecs');

 22 end;
 23 /
Num chars: 24576
Run time: 82 hsecs

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)

---

      6144

SQL> So on the whole nothing I'm going to be too worried about.

Hth

Martin Received on Sat Feb 22 2003 - 20:49:11 CST