Re: Sequence Question

First point - CURRVAL is local to the session, it is always the value that you got on your last call to NEXTVAL, irrespective of whatever anyone else may have been doing.

Secondly - if you are using 8.1 (possibly even 8.0) you do not need to select currval, you can use the RETURNING clause to return the inserted value to a local variable.

    insert into t ...
    returning col_name into M_local_var;

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Jon Strauss wrote in message <2001May2.143457.345_at_npt.nuwc.navy.mil>...


>Hi,

>

>I'd like to create a stored procedure that inserts a new record into a

 table


>using a sequence for the primary key. I'd then like to return the primary

>key value in an out parameter. The only way I've found to do this is to

>first use an insert command and the seq.NEXTVAL for the primary key. Then

>use a select of seq.CURRVAL into the out parameter. The problem I see with

>this is that it's theoretically possible for the seqence value to be

>incremented (by someone else) between the time it is inserted into the

 table


>and then selected into the out parameter. Ideally, I'd like to assign the

>seq.NEXTVAL to a variable and then use that variable in my insert and also

>for the out parameter.  Does anyone know how to do this?

>

>Thanks,

>Jon

>

>