Re: How get the current value from a sequence?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 03 Dec 2004 18:20:22 -0800
Message-ID: <1102126721.196785@yasure>

Craig & Co. wrote:

> 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.

To quote Galen Boyer who said it better than I could:

"Yes you do. Your statement that Daniel is telling you to reconsider is the following:

     I believe that executing the command
             select seq_name.nextval from dual;
     does not increment the sequence (the first time), but defines
     the sequence for the session.

This is incorrect, and there is no explanation other than it is incorrect."

Thanks Galen.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Dec 03 2004 - 20:20:22 CST