Re: BULK COLLECT / INSERT

Looks like you cannot use a subquery with a returning clause only a values clause, that being said you can us a select into and a forall instead.

CREATE OR REPLACE PROCEDURE testreturning AS   TYPE TIdList IS TABLE OF TBL.id%TYPE;
  idList TIdList;
BEGIN  SELECT id bulk collect into idlist FROM tbl; forall i in idlist.first..idlist.last

   insert into tbl2 values(idlist(i));
end;
/

aa

"Achille Carette" <achyl_at_infonie.be> wrote in message news:2b039d5c.0107300822.4c91bcc0_at_posting.google.com...
> I'm facing a pretty annoying error :
> (8.1.7 / Win 2000)
>
> I have two tables, TBL and TBL2, having each one a single field ID.
>
> Here is a procedure, wich compiles without error :
>
> CREATE OR REPLACE PROCEDURE TESTRETURNING AS
> TYPE TIdList IS TABLE OF TBL.ID%TYPE;
> idList TIdList;
> BEGIN
> INSERT INTO TBL2 SELECT ID FROM TBL RETURNING ID BULK COLLECT INTO
 idList;
> END;
> /
>
> When trying to execute this procedure, i get the error :
> ORA-00933: SQL command not properly ended
> ORA-06512: at "TEST.TESTRETURNING", line 5
>
> what's wrong ?

"Achille Carette" <achyl_at_infonie.be> wrote in message news:2b039d5c.0107300822.4c91bcc0_at_posting.google.com...
> I'm facing a pretty annoying error :
> (8.1.7 / Win 2000)
>
> I have two tables, TBL and TBL2, having each one a single field ID.
>
> Here is a procedure, wich compiles without error :
>
> CREATE OR REPLACE PROCEDURE TESTRETURNING AS
> TYPE TIdList IS TABLE OF TBL.ID%TYPE;
> idList TIdList;
> BEGIN
> INSERT INTO TBL2 SELECT ID FROM TBL RETURNING ID BULK COLLECT INTO
 idList;
> END;
> /
>
> When trying to execute this procedure, i get the error :
> ORA-00933: SQL command not properly ended
> ORA-06512: at "TEST.TESTRETURNING", line 5
>
> what's wrong ?
Received on Mon Jul 30 2001 - 13:49:46 CDT