PL/SQL proc - sequences - how to get currval 1

[Custom24]

Hi
I am planning to write a stored procedure which takes in some parameters and inserts records on two tables. In the first table it just inserts one record, and since a trigger is defined on this table, this updates a sequence for the primary key.

The proc then gets the currval of that sequence

select entry_seq.currval into natThisEntryID from dual;

and in the second table inserts a load of related records, and for the foreign key, uses the value it stored in the variable in the line above.

OK, so my question

In the couple of milliseconds between the trigger updating the sequence and the select into statement, it is possible that someone else will call the proc, and increment the sequence. Then it is possible that my first call will end up with the wrong value of the sequence and the related records will all have the wrong foreign key.

Oracle is not my forte, so is this possible?

If so, how to prevent it?

The last time I did it, I wrapped the proc inside a transaction (I need to do this anyway) and I think I read somewhere that this will force any other calls to the proc on other threads to wait their turn, and thus avoid the problem.

Is this correct, and is there a better way of doing it?

I do like to use sequences, so if there is a way which still allows me to use sequences, I would like that.

Thanks for listening!
Mark

 

[Wonboodoo]

Good question. I'm not sure if it will be a problem or not and can't be bothered looking it up.

A safe solution is to have the stored procedure get the sequence.nextval rather than the trigger and then the stored procedure will know what the value is when it inserts into table 2.

You might say that something other process (besides your stored procedure) may insert into the table 1 and then have a null value for the sequence column, but you could still have the trigger do something like:
if :new.col1 is null
then
select entry_seq.currval into :new.col1 from dual;
end if;
to resolve that problem.

 

[ek03]

Another way you can do this without changing the trigger is by adding a returning clause to the insert:

insert into mytable(x, y) valuesa, :b) returning id into myIdVar;

The trigger will populate the id column and the value will be returned into the myIdVar variable, which you can then use for the secondary table.

 

[sem]

Currval is session-specific information. Moreover,all to it will fail, if no call to .nextval was made previously in this session. Regards, Dima

 

[Custom24]

Guys - thanks for all the advice
ek03 - that was exactly what I wanted!
Mark