CODEWITHSUNDEEP
sqloracle-databaseplsql
John Wick
John Wick

Reputation: 745

Trying to get a ID (generated by a trigger/sequence) returned to a variable after a 'Insert into'

I currently have a trigger (with a sequence) set on the VALUE table that auto-generates a new value_id every time a row is inserted into the table. I am trying to return that new value_id (generated by the trigger/sequence) into a variable so that I can use it in an insert into a relationship table later in the proc.

However, when I use the returning statement, Oracle returns an error. When I use a traditional insert, the code seems to run/compile just fine.

Do you know what I may be doing wrong? Below is an abridged version of the code that I wrote: 

insert into value 
  (value_id, 
   energy_product_id, 
   data_source_id, 
   unit_cd, 
   value_tx, 
   hr
  )
  select null, 
         energy_product_id, 
         data_source_id, 
         unit_cd, 
         value_tx
  from value
  returning value_id into v_value_id;

Thanks in Advance

**EDIT: ** Below is the code with the discussed changes. It errors out however:

insert into value 
  (value_id, 
   energy_product_id, 
   data_source_id, 
   unit_cd, 
   value_tx, 
   hr
  )
  select (select seq_sample.nextval from dual), 
         energy_product_id, 
         data_source_id, 
         unit_cd, 
         value_tx
  from value
  returning value_id into v_value_id;

Upvotes: 0

Views: 677

Answers (2)

PKT
PKT

Reputation: 143

I had recently posted a question about the working of Insert into returning and I believe that the answer provided by @APC will work on you case where he mentions that trigger will override https://stackoverflow.com/a/50892580/7071906 assigned value try something like 

 declare
 select  CURSOR c1 IS
 SELECT 1 val,energy_product_id,data_source_id,unit_cd,value_tx from 
  value
 lrec c1%rowtype;
 ldata c1%rowtype;
id number;
 begin
fetch c1 into lrec

ldata.value_id := lrec.id;
ldata.energy_product_id := lrec.energy_product_id;
ldata.unit_cd := lrec.unit_cd;
ldata.value_tx :=  lrec.value_tx;

INSERT INTO my_table
VALUES ldata
RETURNING val1 INTO id;
end;

How does Oracle Insert Into work when order of values is not defined?

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

It seems you can only use the returning into clause if your insert has a values (...) clause, not if you are using a query.

You could possibly use a collection instead:

declare
  type t_rows is table of value_table%rowtype;
  v_rows t_rows;
begin
  select seq_sample.nextval, 
         energy_product_id, 
         data_source_id, 
         unit_cd, 
         value_tx, hr
  bulk collect into v_rows
  from value_table;

  forall i in 1..v_rows.count
    insert into value_table values v_rows(i);

  -- just as debug to see new ID values
  for i in 1..v_rows.count loop
    dbms_output.put_line(v_rows(i).value_id);
  end loop;
end;
/

If you want to retain the trigger and it overrides any passed-in value - which is normal but this might be an exceptional case - then you can use a second collection to get the IDs back, with a bulk-collect returning clause:

declare
  type t_rows is table of value_table%rowtype;
  v_rows t_rows;
  type t_ids is table of number;
  v_ids t_ids;
begin
  select null, 
         energy_product_id, 
         data_source_id, 
         unit_cd, 
         value_tx, hr
  bulk collect into v_rows
  from value_table;

  forall i in 1..v_rows.count
    insert into value_table values v_rows(i)
    returning value_id bulk collect into v_ids;

  -- just as debug to see new ID values
  for i in 1..v_ids.count loop
    dbms_output.put_line(v_ids(i));
  end loop;
end;
/

The indexes for the IDs should correspond with the indexes for the other row data, so you could update the original collection:

  for i in 1..v_rows.count loop
    v_rows(i).value_id := v_ids(i);
  end loop;

But I'm not sure if that's guaranteed. It seems like it should be but I don't recall seeing it documented.

It seems like there should be a more straightforward mechanism to achieve this though...

Upvotes: 1

Related Questions