Re: The Returning Clause

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/08/13
Message-ID: <966202935.21102.0.nnrp-12.9e984b29@news.demon.co.uk>#1/1

It depends on your environment and version: Here is an example for 8.1.5 and pl/sql - the basic trick is to use the BULK COLLECT clause:

drop table junk;
create table junk (n1 number, p1 varchar2(20));

insert into junk values (1,'asdf');
insert into junk values (2,'asdf');
insert into junk values (3,'asdf');
insert into junk values (4,'asdf');

commit;

declare
 type id_tab_type is table of number index by binary_integer;  id_table id_tab_type;

begin
 update junk
 set p1 = 'xcv'
 returning n1 bulk collect into id_table;

 for i in 1..id_table.count loop
  dbms_output.put_line(id_table(i));
 end loop;
end;
.
/

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Sam Habbab wrote in message <3989E67A.3BD81101_at_hasc.com>...


>Hi,

>

>Has anyone been able to use this clause to extract multiple rows?

>

>I'm trying to perform the following:

>

>update table1 set id = NULL for name = sam returning :<something here>;

>

>Then I need to loop over all the updated records and call:

>insert into table2 values (name, id, sysdate, ...);

>

>So far the oracle documentation mentions that you need to use a "bind

>array" but apart from dbms_sql.bind_array, I see no other mention to

>this.

>

>I'm trying to avoid having to perform a select to identify the rows that

>were updated.

>

>Thanks in advance.

>

>S_at_M

>

Received on Sun Aug 13 2000 - 00:00:00 CDT