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