i want to avoid the cursor for loop and just do:

insert select. i dont want to have to insert inside a cursor. But i need certain values selected for later DML.
>
> From: Regis Biassala <Regis.[Email Address Removed] Date: 2003/08/29 Fri PM 01:19:42 EDT
> To: "'[Email Address Removed] <[Email Address Removed] "LazyDBA.com Discussion"
> <[Email Address Removed] Subject: RE: anyway to use a returining into clause on an insert select?
>
> Hey rgaffuri,
> I don't what you want to achieve but anyway have a look at this:
>
> SQL>conn regis/[Email Address Removed]
> SQL> create table t (id int, name varchar2(10));
>
> Table created.
>
> SQL> set serveroutput on
> SQL>
> SQL> declare
> 2 type ttype is table of t.id%type index by binary_integer;
> 3 myttype ttype;
> 4 p_value pls_integer;
> 5 begin
> 6 for i in 1..10 loop
> 7 begin
> 8 p_value := i;
> 9 dbms_output.put_line('p_value is: '||p_value);
> 10 insert into t values(i, 'oracle'||i) returning id bulk collect
> into myttype;
> 11
> 12 for rec in 1..myttype.COUNT loop
> 13 dbms_output.put_line('Current Id inserted is: '||myttype(rec));
> 14 end loop;
> 15
> 16 end;
> 17 end loop;
> 18 end;
> 19 /
> p_value is: 1
> Current Id inserted is: 1
> p_value is: 2
> Current Id inserted is: 2
> p_value is: 3
> Current Id inserted is: 3
> p_value is: 4
> Current Id inserted is: 4
> p_value is: 5
> Current Id inserted is: 5
> p_value is: 6
> Current Id inserted is: 6
> p_value is: 7
> Current Id inserted is: 7
> p_value is: 8
> Current Id inserted is: 8
> p_value is: 9
> Current Id inserted is: 9
> p_value is: 10
> Current Id inserted is: 10
>
> PL/SQL procedure successfully completed.
>
> Regis
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: 29 August 2003 17:52
> To: LazyDBA.com Discussion
> Subject: anyway to use a returining into clause on an insert select?
>
>
> any kind of trick? maybe do it with a pl/sql table dynamicly?
>
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
> *********************************************************************
> This electronic transmission is strictly confidential and intended solely
> for the addressee. It may contain information which is covered by legal,
> professional or other privilege. If you are not the intended addressee,
> you must not disclose, copy or take any action in reliance of this
> transmission. If you have received this transmission in error,
> please notify the sender as soon as possible.
>
> This footnote also confirms that this message has been swept
> for computer viruses.
> **********************************************************************
>
>

Oracle LazyDBA home page