Re: How get the current value from a sequence?

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Fri, 3 Dec 2004 12:27:44 +1100
Message-ID: <41afc15f$0$59200$c30e37c6@ken-reader.news.telstra.net>

Daniel Morgan wrote
>
> I believe you should try it.
>
> CREATE TABLE t (
> numcol NUMBER(5));
>
> CREATE SEQUENCE seq_t;
>
> INSERT INTO t
> (numcol)
> SELECT seq_t.NEXTVAL FROM dual;
>
> SELECT * FROM t;
>
> And perhaps reconsider your response.

Daniel,

I don't need to reconsider my response, the original question was How [to] get the current value from a sequence?

Admittedly he was doing it in a function and yes

    select seq_name.nextval from dual
would increase the value.

So in that case he can use

    select seq_name.currval from dual;
to get the current value in the sequence.

So that you can do the following.

    set serveroutput on size 100000;
    create or replace procedure check_seq     as

        newnum number(4);
    begin

        select seq_t.currval
        into newnum
        from dual;
        dbms_output.put_line ('Curr Seq Num: '||newnum);

        select seq_t.nextval
        into newnum
        from dual;

        insert into t
        values (newnum);

        dbms_output.put_line ('New Seq Num: '||newnum);

    end;
    /

Curr Num: 4
New Num: 5

PL/SQL procedure successfully completed.

Straight from the SQL> prompt - select seq_name.nextval from dual; loads the next value in the sequence into the session. And then to increment it execute the statement again and again and again......
ELSE
now you can

    select seq_name.currval from dual
in your session to keep the current value.

Cheers
Craig. Received on Thu Dec 02 2004 - 19:27:44 CST