Sequence issues

[blakmk]

Does anyone know a way to find the current value of a sequence with using currval or dba/all_sequences?

It seems that use to select sequence.currval one needs to use sequence.nextval first in the session. Heres a script that I used to prove this:

SQL> create sequence myseq start with 22;
Sequence created.
SQL> select myseq.currval from dual;
select myseq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session

SQL>
SQL> select myseq.nextval from dual;

NEXTVAL
----------
22

SQL>
SQL> select myseq.currval from dual;

CURRVAL
----------
22


Weired eh. I'd love to know a way to get round this. Also one may think that dba_sequences.last_number would provide the current number however this only provides the Last sequence number written to disk. "If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used."

Anyone know a way round this?

[jovery]

The START WITH does not actually initialise the sequence, you need to call the sequence with the NEXTVAL in order to do this. The first time this is run it will give the START WITH value and once this has been done you can retrieve the CURRVAL at any time.

Regards

[blakmk]

>"The START WITH does not actually initialise the sequence, you need >to call the sequence with the NEXTVAL in order to do this."

It seems to be that an Innvocation of nextval is only valid for a session not for subsequent sessions. The problem is that I dont want to have to use nextval everytime I open a new session to the database. The reason for this is that nextval increments the sequence. All I want to do is see the value not change it.

[jmodic]

SELECT nextvalue FROM v$_sequences
WHERE sequence_owner = 'SCOTT' AND sequence_name = 'MY_SEQ'
AND cache_size > 0
UNION
SELECT last_number AS nextvalue FROM dba_sequences
WHERE sequence_owner = SCOTT' AND sequence_name = 'MY_SEQ'
AND cache_size = 0;

Check the following thread for more information about this isue: http://www.dbasupport.com/forums/sho...threadid=12471

[blakmk]

Thanks for that jurij.

Its a bit of a bummer that v$_sequences is only accesible to sys though. I guess the only way round it is to use a publically accesible stored proc that uses this view.

[jmodic]

Originally posted by blakmk
... I guess the only way round it is to use a publically accesible stored proc that uses this view.

Yep, I guess so too.

[blakmk]

Does anyone know if this problem occurs in 9i also?