Re: Sequence question

From: Candido Dessanti <candido.dessanti_at_blunet.it>
Date: 25 Feb 2003 09:28:19 -0800
Message-ID: <11c1e2d8.0302250928.7a105289@posting.google.com>

> 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

Yes, that's correct. Oracle return the current value of the sequence for the session if you ask for CURRVAL pseudo column; you have to get a value for the session with NEXTVAL first.

> My question
> is; is there a way to determine the current value of a sequence without
> doing a NEXTVAL first?

Yap. You have to query the XXX_SEQUENCES views (DBA,ALL,USER). THe column containing the last used # in the sequence is called highwater. So

SELECT highwater
FROM user_sequences
WHERE sequence_name='Your Sequence';

Anyway you should use it with care because after you ran your query another session could use the same sequence. SO you would use this view for information only or if you are sure no one is using the sequence you are interested in.

> 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.

Not in my knowledge ;)

ciao. Received on Tue Feb 25 2003 - 11:28:19 CST