Getting Inserted Sequence Value using DBI

[frasernm]

Hi,

We're using perl dbi and oracle (via a proxy server).

We have a number of tables where the primary key is a sequence. When we insert a row to one of these tables we need to find the value of the sequence inserted so we can use that in other queries. We've tried a few methods, but without much success.

Anybody know the best way to do this?

TIA

Fraser

 

[MikeLacey]

Fraser,

In the past I've done this in three different ways.

1 - In the Insert statement I've had something like:

Insert Into MYTABLE (PKEY, A_FIELD) Values(
SEQUENCENAME.NextVal, 'some data'
)

I haven't done this using the DBI but I don't have a reason to suppose it wouldn't work. This way is ok if all you are doing is inserting a single row in one table.

2 - If you need to know what value is going in there, select the value from the dummy table first and then use that.

3 - The way I finally ended up doing it was to create a trigger on each table that used a primary filled with a sequence. The trigger only needs to run on insert, set :new.PKEY to SEQUENCENAME.NextVal. Sometimes you'll need to manually fix the value of PKEY so it's wise, in the trigger, to only set :new.PKEY if it is NULL - so that you can set it by hand if you need to. Mike
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[frasernm]

Thanks,

I've toyed with both 2 and 3 (3 would be great *if* we could get the value back).

re 2 - what if you have more than one concurrent user?

Fraser

 

[MikeLacey]

with 2 - doesn't matter, each person will get their own number.

duh..... I forgot.

as well as NextVal there's something called CurrVal

once you've called NextVal you can check CurrVal to see what value it gave *you* (not anyone else) in the current transaction

and that will work with all three options Mike
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.