I have a procedure that is executed when a new record is inserted into table A. This procedure then inserts the record into table B and populates an ID column using a sequence that gets the nextval in a trigger. The procedure is trying to copy that ID value back a column in table A.
The procedure was first lookiing for the currval. But because nextval is never called in procedure it doesn't work. So it was changed to get nextval. Only this doesn't work because after the insert into table B the nextval increments by 1. So the number entered into id in table B is never the same as the number we try to update the column in table A with.
Here is the section of the procedure. I omitted insert column names and variable declarations datatypes etc
execute immediate l_insert_stmt;
EXECUTE IMMEDIATE 'SELECT ' ||targetSchema ||'.seq_bib.nextval FROM dual' INTO iCounter;
l_update_stmt := 'UPDATE ' || sourceSchema || '.tableA = ' || iCounter || ' WHEREtableB_ id = ' || stiRecordId;
execute immediate l_update_stmt;
how can i get the id value for table b entered into table a during the update