bulk collect in the "returning into " clause works in 10g,
not sure about 9i,
definetily doesn 't in 8i
Igor Neyman, OCP DBA
ineyman@(protected)
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Davey, Alan
Sent: Tuesday, August 17, 2004 1:26 PM
To: oracle-l@(protected)
Subject: PL/SQL Question
Hi,
Is it possible to use bulk collect in the "returning into " clause when
doing an insert as select?
Oracle 8.1.7
SQL > create table test_bulk (x integer, y varchar2(10));
Table created.
SQL > ed
Wrote file afiedt.buf
1 declare
2 type my_type is table of number;
3 my_list my_type;
4 begin
5 insert into test_bulk (x, y)
6 select rownum, substr(object_name,1,10)
7 from all_objects
8 returning x bulk collect into my_list;
9* end;
SQL > /
declare
*
ERROR at line 1:
ORA-00933 (See ORA-00933.ora-code.com): SQL command not properly ended
ORA-06512 (See ORA-06512.ora-code.com): at line 5
The returning clause seems to work just fine for other DML statements,
so I know that the datatypes are declared correctly:
SQL > insert into test_bulk (x,y)
2 select rownum, substr(object_name,1,10)
3 from all_objects
4 where rownum < 10;
9 rows created.SQL > ed
Wrote file afiedt.buf
1 declare
2 type my_type is table of number;
3 my_list my_type;
4 begin
5 update test_bulk
6 set y =3D 'XXX '
7 returning x bulk collect into my_list;
8 for i in my_list.first..my_list.last
9 loop
10 dbms_output.put_line(my_list(i));
11 end loop;
12* end;
SQL > /
1
2
3
4
5
6
7
8
9
PL/SQL procedure successfully completed.
Looking at the documentation, I don 't see mention that bulk collect is
not allowed with a insert as select statement, but all examples only
seem to show it when using the values clause. =0D
My goal is to insert a bunch of records and keep a list of the primary
key Ids, possibly do some other actions with that list, and then finally
return that list to the calling program.
Short of adding an indicator column for these new inserts and then
having to issue another select to retrieve the Ids, is there anyway to
accomplish what I am trying to do more cleanly? Hopefully, I am just
missing something obvious.
Thanks,
Alan
"This information in this e-mail is intended solely=0D
for the addressee and may contain information=0D
which is confidential or privileged. Access to this
e-mail by anyone else is unauthorized. If you=0D
are not the intended recipient, or believe that=0D
you have received this communication in error,=0D
please do not print, copy, retransmit,=0D
disseminate, or otherwise use the information.=0D
Also, please notify the sender that you have=0D
received this e-mail in error, and delete the=0D
copy you received. "
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
