Oracle: insert using select is not returning bulk collect newly inserted ids

oracle, plsql, plsqldeveloper

Solution

No, you wont be able to make it work that way. You can use `returning` (not bulk collect) clause only when `insert` statement uses `values` clause.

You can use this kind of work around to get those `id`s:

You first populate a collection with values you want to insert, and second use `forall` construct to insert the data and return `id`s into another collection:

/* identity column is a 12c feature. In prior versions you use 
   sequences - not the main point here. Use it just to save time.
 */   
create table t1(
  t1_id number generated as identity primary key,
  name1 varchar2(31),
  age   number
) ;

Pl/SQL Block:

declare
  /* record */
  type t_rec is record(
    name1 varchar2(32),
    age   number  -- as a side note, it's better to store 
  );              -- date of birth not the age - not that dependable.

  type t_source_list is table of t_rec;
  type t_id_list     is table of number;

  l_source_list t_source_list; -- source collection
  l_id_list     t_id_list;     -- collection we are going to put IDs into
begin

  /* data we are going to insert 
    replace this query with yours */
  select dbms_random.string('l', 7)
       , level
    bulk collect into l_source_list
   from dual
  connect by level <= 11;

  /* insert data and return IDs into l_id_list collection */
  forall i in l_source_list.first..l_source_list.last
    insert into t1(name1, age) 
       values(l_source_list(i).name1, l_source_list(i).age)
    returning t1_id bulk collect into l_id_list;

  /* print the list of new IDs. */
   for i in l_id_list.first .. l_id_list.last loop
     dbms_output.put_line('ID #' || to_char(I)||':  '||to_char(l_id_list(i)));
   end loop;  
end;
/

Result:

anonymous block completed
ID #1:  1
ID #2:  2
ID #3:  3
ID #4:  4
ID #5:  5
ID #6:  6
ID #7:  7
ID #8:  8
ID #9:  9
ID #10: 10
ID #11: 11

Problem

Table Name: `T_record` ``` Id(auto increment using sequence) Name Age 1 Chitta 18 2 Chitta1 19 3 Chitta2 18 4 Chitta3 18 ``` I have PL/SQL procedure, which will insert records in the above table. ``` Insert into T_record (name, Age) (select name, age from T_record where Age =18) returning id bulk collect into v_newly_added_conf_input_ids; ``` But bulk collect is not working. How will I get to know the `id`s, which are inserted newly(I want it to use the same `id`s in other select query)?

Original source