Re: How get the current value from a sequence?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sun, 05 Dec 2004 19:45:05 -0800
Message-ID: <1102304604.191461@yasure>

Craig & Co. wrote:

>>"Yes you do.  Your statement that Daniel is telling you to
>>reconsider is the following:
>>
>>     I believe that executing the command
>>             select seq_name.nextval from dual;
>>     does not increment the sequence (the first time), but defines
>>     the sequence for the session.
>>
>>This is incorrect, and there is no explanation other than it is
>>incorrect."
>>
>>Thanks Galen.
>>-- 
>>Daniel A. Morgan

>
>
> Hi,
>
> When I setup a sequence and exit the database.
> Come back in and execute
> select seq_name.nextval from dual;
>
> It does not increment the sequence number, UNTIL I execute the command
> again.
>
> If I don't want to increment the sequence, but I still want to find out what
> the value
> is that I would execute the command
> select seq_name.currval from dual;
>
> If I attempt to execute the 'currval' command, BEFORE the 'nextval' command
> I get the message
> ERROR at line 1:
> ORA-08002: sequence SEQ_NAME.CURRVAL is not yet defined in this session
>
> Which is
>
> 08002, 00000, "sequence %s.CURRVAL is not yet defined in this session"
> // *Cause: sequence CURRVAL has been selected before sequence NEXTVAL
> // *Action: select NEXTVAL from the sequence before selecting CURRVAL
>
> So all in all - I do not need to reconsider my response.
>
> Cheers
> Craig.

You are ignoring advice given to you multiple times, in the group's archives and well documented on many web sites. So here it is again:

CURRVAL only works within the current session following a NEXTVAL.

Once you end the session CURRVAL is meaningless until you again use NEXTVAL which increments the counter yet again.

If you want the current value of a sequence when first logging on the only way to do so is a query against one of the xxx_sequence data dictionary views.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sun Dec 05 2004 - 21:45:05 CST