Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

RETURNING clause in INSERT INTO SELECT

mennanSep 19 2006 — edited Sep 19 2006
I want to get id of new inserted records in collection. But I could not achieve it. Why the statament is not executing?

Kindly Regards...


Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 
Connected as sysadm


SQL> set serveroutput on
SQL> 
SQL> drop table my_t;

Table dropped

SQL> create table my_t( x number);

Table created

SQL> DECLARE
  2    TYPE ttt_tab IS TABLE OF my_t.x%TYPE;
  3  
  4    ttt_list ttt_tab;
  5  BEGIN
  6    INSERT INTO my_t
  7      (x)
  8      SELECT rownum FROM all_objects WHERE rownum < 100 RETURNING x BULK COLLECT INTO ttt_list;
  9  END;
 10  /

DECLARE
  TYPE ttt_tab IS TABLE OF my_t.x%TYPE;

  ttt_list ttt_tab;
BEGIN
  INSERT INTO my_t
    (x)
    SELECT rownum FROM all_objects WHERE rownum < 100 RETURNING x BULK COLLECT INTO ttt_list;
END;

ORA-06550: line 8, column 55:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored

SQL> 
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 17 2006
Added on Sep 19 2006
6 comments
22,009 views