Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL Question

Re: PL/SQL Question

From: Melanie Caffrey <melanie_caffrey_at_yahoo.com>
Date: Tue, 17 Aug 2004 12:38:52 -0700 (PDT)
Message-ID: <20040817193852.65772.qmail@web51907.mail.yahoo.com>


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;

   end;

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."
>



> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>


> To unsubscribe send email to:
> oracle-l-request_at_freelists.org
> 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_at_freelists.org 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
-----------------------------------------------------------------
Received on Tue Aug 17 2004 - 14:34:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US