| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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: SQL command not properly ended
ORA-06512: 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."
 |
 |