Re: Prompting multiples times in PL/SQL??

From: CN <cneumuell_at_yahoo.de>
Date: Thu, 22 Jun 2006 14:10:00 +0200
Message-ID: <newscache$dqi91j$25k$1@nntp.liwest.at>

sybrandb wrote:
> qazmlp1209_at_rediffmail.com wrote:
>

>>The Table format is as follows:
>>
>>SQL> desc MyTEST;
>> Name                                      Null?    Type
>> ----------------------------------------- --------
>>----------------------------
>> NAME                                               VARCHAR2(10 CHAR)
>>
>>I want to add 10 rows into this Table, with each row containing the
>>'name' that I want to enter.
>>The following PL/SQL script does not work:
>>
>>----------
>> declare
>>  i number;
>> begin
>>          for i in 1..10 loop
>>               insert into tst (name) values ('&name');
>>          end loop;
>>    end;
>>----------
>>
>>It prompts for the 'name' only once and the same name figures in all
>>the rows. But, I want to be prompted 10 times and each time I want to
>>enter different 'name'. How exactly I should adapt the PL/SQL script
>>for this?

>
>
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
> insert into tst(name) values('&name');
>
>

Right, but it's an ugly duplication. That's why I'd prefer

declare

   v_names v2_tbl := v2_pkg.split('&name',','); begin

   FOR i in 1 .. v_names.count LOOP
     insert into tst(names) values (v_names(i));    END LOOP;
end;
/

Btw:

create type v2_tbl as table of varchar2(4000) /

create or replace package v2_pkg as

   function split(i_str in varchar2,i_separator in varchar2) return v2_tbl; end;
/
create or replace package body v2_pkg as

   function split(i_str in varchar2,i_separator in varchar2) return v2_tbl    is

     v_tbl v2_tbl := v2_tbl();
     v_start number := 1;
     v_sep_length number := length(i_separator);
     v_sep_pos number;
   begin
     IF i_str is null THEN return v_tbl; END IF;

     LOOP
       v_sep_pos := instr(i_str,i_separator,v_start);
       v_tbl.extend;
       IF v_sep_pos = 0 THEN
         v_tbl(v_tbl.count) := substr(i_str,v_start);
         exit;
       ELSE
         v_tbl(v_tbl.count) := substr(i_str,v_start,v_sep_pos-v_start);
         v_start := v_sep_pos + v_sep_length;
       END IF;
     END LOOP;

     return v_tbl;

   end;
end;
/

Hth,
Chris Received on Thu Jun 22 2006 - 07:10:00 CDT