CODEWITHSUNDEEP
sqloracle-databasesequence
frno
frno

Reputation: 2871

How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it.

Thanks.

EDIT AND CONCLUSION

As stated by Justin Cave It's not useful to try to "save" sequence number so

select a_seq.nextval from dual;

is good enough to check a sequence value.

I still keep Ollie answer as the good one because it answered the initial question. but ask yourself about the necessity of not modifying the sequence if you ever want to do it.

Upvotes: 217

Views: 750663

Answers (8)

Chamith
Chamith

Reputation: 129

We can use the below query to get the sequence next value.

select last_number from dba_sequences where sequence_name = '<sequence name>';

Upvotes: 3

Ollie
Ollie

Reputation: 17538

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = '<sequence owner>'
   AND sequence_name = '<sequence_name>';

You can get a variety of sequence metadata from user_sequences, all_sequences and dba_sequences.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

If you want all the metadata then:

SELECT *
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

SELECT increment_by I
  FROM user_sequences
 WHERE sequence_name = 'SEQ';

      I
-------
      1

SELECT seq.nextval S
  FROM dual;

      S
-------
   1234

-- Set the sequence to decrement by 
-- the same as its original increment
ALTER SEQUENCE seq 
INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S
  FROM dual;

      S
-------
   1233

-- Reset the sequence to its original increment
ALTER SEQUENCE seq 
INCREMENT BY 1;

Sequence altered.

Just beware that if others are using the sequence during this time - they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

Also, you might want to set the cache to NOCACHE prior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.

Upvotes: 219

caot
caot

Reputation: 3318

The follows is often used:

select field_SQ.nextval from dual;  -- it will increase the value by 1 for each run
select field_SQ.currval from DUAL;

However the following is able to change the sequence to what you expected. The 1 can be an integer (negative or positive)

alter sequence field_SQ increment by 1 minvalue 0

Upvotes: 11

Fabien Haddadi
Fabien Haddadi

Reputation: 2080

If your use case is that some backend code inserts a record, then the same code wants to retrieve the last insert id, without counting on any underlying data access library preset function to do this, then, as mentioned by others, you should just craft your SQL query using SEQ_MY_NAME.NEXTVAL for the column you want (usually the primary key), then just run statement SELECT SEQ_MY_NAME.CURRVAL FROM dual from the backend.

Remember, CURRVAL is only callable if NEXTVAL has been priorly invoked, which is all naturally done in the strategy above...

Upvotes: 2

Roland
Roland

Reputation: 5226

I also tried to use CURRVAL, in my case to find out if some process inserted new rows to some table with that sequence as Primary Key. My assumption was that CURRVAL would be the fastest method. But a) CurrVal does not work, it will just get the old value because you are in another Oracle session, until you do a NEXTVAL in your own session. And b) a select max(PK) from TheTable is also very fast, probably because a PK is always indexed. Or select count(*) from TheTable. I am still experimenting, but both SELECTs seem fast.

I don't mind a gap in a sequence, but in my case I was thinking of polling a lot, and I would hate the idea of very large gaps. Especially if a simple SELECT would be just as fast.

Conclusion:

  • CURRVAL is pretty useless, as it does not detect NEXTVAL from another session, it only returns what you already knew from your previous NEXTVAL
  • SELECT MAX(...) FROM ... is a good solution, simple and fast, assuming your sequence is linked to that table

Upvotes: 1

Ainsworth
Ainsworth

Reputation: 79

This is not an answer, really and I would have entered it as a comment had the question not been locked. This answers the question:

Why would you want it?

Assume you have a table with the sequence as the primary key and the sequence is generated by an insert trigger. If you wanted to have the sequence available for subsequent updates to the record, you need to have a way to extract that value.

In order to make sure you get the right one, you might want to wrap the INSERT and RonK's query in a transaction.

RonK's Query:

select MY_SEQ_NAME.currval from DUAL;

In the above scenario, RonK's caveat does not apply since the insert and update would happen in the same session.

Upvotes: 3

georgejo
georgejo

Reputation: 25

My original reply was factually incorrect and I'm glad it was removed. The code below will work under the following conditions a) you know that nobody else modified the sequence b) the sequence was modified by your session. In my case, I encountered a similar issue where I was calling a procedure which modified a value and I'm confident the assumption is true.

SELECT mysequence.CURRVAL INTO v_myvariable FROM DUAL;

Sadly, if you didn't modify the sequence in your session, I believe others are correct in stating that the NEXTVAL is the only way to go.

Upvotes: -2

RonK
RonK

Reputation: 9652

select MY_SEQ_NAME.currval from DUAL;

Keep in mind that it only works if you ran select MY_SEQ_NAME.nextval from DUAL; in the current sessions.

Upvotes: 157

Related Questions