Phil Kaufman wrote:
> Thanks very much for your reply Sybrand.
>
> It seems that one can't use the system table to determine the current value
> of a sequence that is defined with a cache (as ours is, and is the database
> engine default), since the 'last_number' column of the system view (in both
> user and dba views) references the latest cached value, not the current last
> used value.
>
> Anyhow, thanks anyway for your suggestion.
>
>
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:i6nk5v4e7r4afhg0bk2pdch0dvij9vl6fb_at_4ax.com...
>
>>On Mon, 24 Feb 2003 12:05:42 -0500, "Phil Kaufman"
>><philk_at_dbcsmartsoftware.com> wrote:
>>
>>
>>>Hi everyone,
>>>
>>>This is a sequence question. I'm using Oracle 8.0.5, 8i, and 9i database
>>>engines.
>>>
>>>I have a question concerning sequences. I get the following error
>>
> message
>
>>>if I attempt to login into the database, and determine the current value
>>
> of
>
>>>a sequence by executing the following statement:
>>>
>>>select seq_some_sequence.currval from dual
>>> *
>>>ERROR at line 1:
>>>ORA-08002: sequence SEQ_SOME_SEQUENCE.CURRVAL is not yet defined in this
>>>session
>>>
>>>I've determined that I must first get the next sequence value from the
>>>database engine first before doing a CURRVAL on the sequence. My
>>
> question
>
>>>is; is there a way to determine the current value of a sequence without
>>>doing a NEXTVAL first?
>>>
>>>Also, I don't remember this being an issue with pre 8 databases, though I
>>>can't be sure of that. Not sure if things have changed with sequences
>>
> since
>
>>>version 7.3.4.
>>>
>>>Anyway, any response is welcomed.
>>>
>>>Thanks in advance.
>>>
>>
>>
>>The answer to the first question is no, unless you retrieve the
>>current value from the dictionary, which should be considered bad
>>programming practice, and is available in the dba_sequences view only,
>>IIRC.
>>
>>And yes: it has ALWAYS worked that way, in ANY version
>>
>>Regards
>>
>>
>>Sybrand Bakker, Senior Oracle DBA
>>
>>To reply remove -verwijderdit from my e-mail address
Even if you could see the cache of sequence values in memory,
you cannot reliably determine the current (last used) value
because Oracle does not guarantee that sequences will not have
holes -- including where there is no caching. If the sequence
was flushed from the buffer, if the server was brought down and
up, if the prior query getting the NextVal from the sequence rolled
back you will have a hole in your sequence.
Perhaps if you posted the business problem you are trying to solve,
someone on this board could offer a suggestion.
--
AJ Allen
Received on Mon Feb 24 2003 - 16:42:17 CST
