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

Question

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;

What Oracle version? Please post a [mcve] Are you not using a sequence to generate the value? — OldProgrammer, Jun 19 '18 at 18:46
11.2.0 . I am using a sequence/trigger to generate the value. but i need to return that value into a variable so i can use it in another insert later. — John Wick, Jun 19 '18 at 18:49
@OldProgrammer i edited my question , thanks for the heads up. did i make the correct edits as specified in that link? — John Wick, Jun 19 '18 at 18:51
So instead of a trigger, get the seq.nextval in the procedure and then you can pass into multiple inserts. — OldProgrammer, Jun 19 '18 at 18:53
that would be incorrect because then i would get the next value after the one i inserted . i need that exact value. For example: (1st Table) Value_ID (1) Value_TX(5) --- (2nd Table) Value_ID(1) Translation_ID(1) CREATE_DT(sysdate). I need the '1' value_id to show up in both tables/inserts — John Wick, Jun 19 '18 at 18:53
No you would not. Get rid of the trigger, and get the sequence.nextval in the procedure, and use it for your inserts into the two different tables. — OldProgrammer, Jun 19 '18 at 19:24
i get an error saying that I can't use a sequence in that insert — John Wick, Jun 19 '18 at 19:50
Posting actual command you ran might help. Do edit the initial message and post it there, not in a comment. — Littlefoot, Jun 19 '18 at 19:54
thanks, i posted the actual command/changes that i made to the code — John Wick, Jun 19 '18 at 20:05
"returns an error" and "errors out" are not helpful, please include the full error messages in the question. You don't need a subquery to get the sequence value, incidentally. It looks like you will insert multiple rows though, so if `v_value_id` is a scalar variable are you expecting all the new IDs to go into that? — Alex Poole, Jun 19 '18 at 20:18
@AlexPoole yes, every single id must be stored and then transferred to a reference table. Also, the error i am getting is: Error(45,33): PLS-00103: Encountered the symbol "VALUE_ID" when expecting one of the following: := . ( @ % ; . — John Wick, Jun 19 '18 at 21:02
The code just won't compile with both 1) that type of insert and 2) my requirement of having to have that id (generated) to be stored in a table via a 'returning' command. — John Wick, Jun 19 '18 at 21:03

score 1 · Accepted Answer · answered Jun 19 '18 at 22:32

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

PKT · Answer 2 · 2018-06-20T14:38:17.690

1

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?

edited Jun 20 '18 at 14:38

answered Jun 20 '18 at 03:53

PKT

143
8

thanks, yeah i can get a traditional insert to work but not a insert into select from to return anything... (if that makes sense) – John Wick Jun 20 '18 at 14:55
In that case this stackoverflow thread [link](https://stackoverflow.com/questions/5325033/plsql-insert-into-with-subquery-and-returning-clause-oracle) already points out solution for your question and looks like is exactly what you are asking) – PKT Jun 20 '18 at 15:05
Ah... thanks @prakriti , it looks like what i'm asking is not possible :( – John Wick Jun 20 '18 at 16:15

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

2 Answers2