| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL Question
Hi Alan,
The functionality you'd like doesn't appear to be supported in 8.1.7.
Though it's not quite as nice and neat as the single statement you have below, would something like the following help get you partially there?
declare
type my_type is table of number;
type my_type_string is table of varchar2(10);
my_list my_type;
my_list_string my_type_string;
begin
insert into test_bulk (x, y)
select rownum, substr(object_name,1,10)
bulk collect into
my_list, my_list_string
from all_objects
where rownum < 10;
Then insert them into test_bulk using "forall i" functionality.
Yeah, I know. Not quite what you were looking for.
Cheers,
Melanie
>
> 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."
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Aug 17 2004 - 14:34:32 CDT
 |
 |