12 Replies Latest reply on Feb 8, 2006 7:50 AM by 424871

    current value of a sequence

    407048
      Is there a way to get the current value of a sequence (other than saying a
      select seq.nextval from dual and then, select seq.currval from dual). I just didn't want to bump up the sequence by saying nextval but needed to find the current value.

      My sequence was created with options cache 20 , increment by 1
        • 1. Re: current value of a sequence
          Dr.Dimitri
          You don't need to execute a seq.nextval. Just say seq.currval. If you have more then one session using that sequence at the same time you can only get the current value of your session.

          Dim
          • 2. Re: current value of a sequence
            Jens Petersen
            Dimitri,
            to get the current value of your session you must had retrieved seq.nextval at least once before.
            • 3. Re: current value of a sequence
              481870
              No, currval only works if nextval was before called in the same sessions :
              SQL> create sequence myseq;

              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> select myseq.nextval from dual;

                 NEXTVAL
              ----------
                       1

              SQL> select myseq.currval from dual;

                 CURRVAL
              ----------
                       1

              SQL>
              You should consider querying USER_SEQUENCES (column LAST_NUMBER)
              • 4. Re: current value of a sequence
                Jens Petersen
                Can't you just try to select seq.currval and in case an ORA-08002 is raised select seq.nextval
                • 5. Re: current value of a sequence
                  481870
                  select Sequence_Name, Last_Number from user_sequences;

                  SEQUENCE_NAME                  LAST_NUMBER
                  ------------------------------ -----------
                  MYSEQ                                   21
                  will ever work ;-)
                  • 6. Re: current value of a sequence
                    483122
                    You should consider querying USER_SEQUENCES (column LAST_NUMBER)
                    Cause he is using cache option, you can't use last_number to find current value:
                    SQL> select sequence1.nextval from dual;

                       NEXTVAL
                    ----------
                            11

                    SQL> select sequence1.currval from dual;

                       CURRVAL
                    ----------
                            11

                    SQL> select sequence_name, last_number from user_sequences
                      2  /

                    SEQUENCE_NAME                  LAST_NUMBER
                    ------------------------------ -----------
                    SEQUENCE1                               21

                    SQL>
                    Regards,
                    Reza
                    • 7. Re: current value of a sequence
                      481870
                      Yes, but not sure... :-/

                      If the sessin has never call the sequence.nextval, the user_sequence is giving the number he would get if he call nextval....
                      • 8. Re: current value of a sequence
                        483122
                        If you don't use cache option you will get next value from sequence if you query LAST_NUMBER from user_sequences if other user don't user sequence same time!
                        SQL> select sequence2.nextval from dual;

                           NEXTVAL
                        ----------
                                 4

                        SQL> select sequence2.currval from dual;

                           CURRVAL
                        ----------
                                 4

                        SQL> select sequence_name, last_number from user_sequences;

                        SEQUENCE_NAME                  LAST_NUMBER
                        ------------------------------ -----------
                        SEQUENCE2                                5
                        Regards,
                        Reza
                        • 9. Re: current value of a sequence
                          407048
                          Right, so the conclusion I guess is that since the cache is 20, without using seq.nextval, there is no exact calculation we can arrive at ??
                          • 10. Re: current value of a sequence
                            424871
                            Jens gave you what I believe to be the right solution.....
                            Can't you just try to select seq.currval and in case an ORA-08002 is raised select seq.nextval
                            • 11. Re: current value of a sequence
                              481870
                              I don't totally agree...

                              Doing this is not only querying the current value, asking for nextval is changing the sequence !!!
                              • 12. Re: current value of a sequence
                                424871
                                Yeah, I guess it really depends on precisely why you want this information and the nature and frequency of the application that uses. If it is a highly active connection pool, then using the curval-nextval method would usually work on the first time. ...Kinda analogous to soft parse/hard parse.

                                But perhaps it might just make the most sense to use nextval 100% of the time. Why does it really matter if we change the sequence? Maybe the last guy rolledback or the instance was just restarted and the cache was lost.