Re: QUESTION ON SEQUENCE VALUES - 9iR2

Jeremy wrote:
> Web based app (using mod_plsql)
>
> At the start of processing, an activity ID is retrieved from a sequence:
>
> select act_s.nextval
> into l_act_id
> from dual;
>
> This value is then used as part of an insert statement to log the start
> of a procedure's execution.
>
> At the end, this code is executed:
>
> select act_s.currval
> into l_act_id
> from dual;
>
> and then an update statement is executed to update the row whose pk is
> l_act_id.
>
> The question is this: are there *any* circumstances (that anyone has
> heard of or come across) where the value returned by the 2nd select
> would not be the same value as returned by the first?
>
> This is the same sessionid and, as far as I know, the package state (all
> packages used) is automatically reset at the end of each execution.
>
> Appreciate any thoughts
>
>

For these types of situations, after I query NEXTVAL, I put the value into a variable so that I can recall it later. This way, I guarantee that I do not lose my sequence's value.

The CURRVAL value should survive for the duration of your session. But some apps like many web apps do not always use the same session for the user. This is one of my primary drivers for storing the sequence value in a variable.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com