Re: retreiving CurrVal in a select statement.

From: Peter Rak <prak_at_vsz.sk>
Date: 1997/12/03
Message-ID: <34855F97.B9F90BC2@vsz.sk>#1/1

Hi,
Sorry, bu is it not true, unless sequence is created or altered as nocached. Look:

SQL> create sequence abc;
Sequence created.
SQL> select last_number from user_sequences where sequence_name = 'ABC'; LAST_NUMBER

---

          1
SQL> select abc.nextval from dual;
  NEXTVAL

---

        1
SQL> select last_number from user_sequences where sequence_name = 'ABC'; LAST_NUMBER

---

         21
SQL> select abc.nextval from dual;
  NEXTVAL

---

        2
SQL> select last_number from user_sequences where sequence_name = 'ABC'; LAST_NUMBER

---

         21
SQL> select abc.nextval from dual;
  NEXTVAL

---

        3
SQL> select last_number from user_sequences where sequence_name = 'ABC'; LAST_NUMBER

---

         21
...
But:
Last_number points to next value.

SQL> create sequence aaa nocache;
Sequence created.
SQL> select last_number from user_sequences where sequence_name='AAA'; LAST_NUMBER

---

          1
SQL> select aaa.nextval from dual;
  NEXTVAL

---

        1
SQL> select last_number from user_sequences where sequence_name='AAA'; LAST_NUMBER

---

          2
SQL> select aaa.nextval from dual;
  NEXTVAL

---

        2
SQL> select last_number from user_sequences where sequence_name='AAA'; LAST_NUMBER

---

          3
SQL> select aaa.nextval from dual;
  NEXTVAL

---

        3
SQL>         Peter.

Frédéric Trébuchet wrote:

> Hi Kal,
>
> Current value for a sequence is accessible via a "select seq_name.currval
> from dual" only after a new sequence number have been getted via a "select
> seq_name.nextval from dual" within the same session (unless you'll have an
> ORA-08002).
>
> If you want to know which number was distributed last time a sequence was
> used, use something like "select last_number from user_sequences where
> sequence_name = "SEQ_NAME".
>
> Have a look to user_sequences, dba_sequences and all_sequences views for
> detailed informations about sequences in your database.
>
> TRF
>
> Kal Khatib <kal_at_gene.COM> a écrit dans l'article
> <34835F93.EE2D03B0_at_gene.com>...
> > How do you get the current value of a sequence in a select statement;
> > I try;
> > select CurrVal from some_sequence
> > I get
> > ORA-02201: sequence not allowed here
> >
> > is it possible using the above method? Or is there another way? I'm
> > trying to assign transactions a unique sequence, then pass the assigned
> > sequence to the customer for their reference in the same sesison.
> >
> > thanks for any help.
> > Kal.
> >
> >
Received on Wed Dec 03 1997 - 00:00:00 CST