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

Question

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

User · Answer

The follows is often used  select field SQ nextval from dual  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

User · Answer

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

User · Answer

SELECT last number   FROM all sequences  WHERE sequence owner     lt sequence owner gt      AND sequence name     lt sequence name gt      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     lt sequence name gt      If you want all the metadata then   SELECT     FROM user sequences  WHERE sequence name     lt sequence name gt      Hope it helps     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to sequence