Re: Subsequent call to seq_name.nextval. Please help.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 23 Jun 2001 07:29:46 -0700
Message-ID: <9h294q0i9h@drn.newsguy.com>

In article <ad3752b9.0106221803.788e785d_at_posting.google.com>, boltsilver_at_hotmail.com says...
>
>Thanks Thomas and to everyone else who replied.
>selecting twice, or closing/opening the cursor before opening it again
>did the trick.
>
>I cannot use create sequence because I am altering an existing
>sequence to be incremented by two, and use ONLY even numbers.
>

well, I could see many cases where you will be thwarted using your logic below in a multi-user environment. say the sequence is currently at 100.

time      Session 1                  Session 2
t1        get nextval=101
t2                                   get nextval=102 SUCCESS!
t3        get nextval=103 FAILURE!

Guess if you really really really couldn't alter the existing sequence (don't see why you cannot? just get it to an even number and then alter it to increment by 2) you should really:

begin

   loop

      for x in ( select seq.nextval nv from dual ) 
      loop
          if mod( x.nv, 2 ) = 0 ) then
             return x.nv;
          end if;
      end loop;

   end loop;
end;
/

that should eventually terminate and return an even sequence number. I know, bad practice to return from a loop but this requirement (even sequence without using a sequence that generates even numbers) isn't really "good" either.

>- Silver
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9gvle002674_at_drn.newsguy.com>...
>> In article <ad3752b9.0106211313.66bd170c_at_posting.google.com>,
>> boltsilver_at_hotmail.com says...
>> >
>> >Hi Experts,
>> >
>> >The manual says: "Oracle will only increment the sequence once in a
>> >given SQL statement, so if a statement contains multiple references to
>> >NEXTVAL, the second and subsequent reference will return the same
>> >value as CURRVAL."
>> >
>> >But I want the next call to nextval to be really the nextval. How can
>> >I achieve this ? Below is a sample code. The second fetch retains the
>> >same odd number that is obtained in previous fetch.
>> >
>> >set serveroutput on;
>> >declare
>> > val number := 0;
>> > cust_id_not_even exception;
>> > cursor c_cust_id is select seq_customer_id.nextval from dual;
>> >begin
>> > open c_cust_id;
>> > fetch c_cust_id into val;
>> > if mod(val,2) = 1 then
>> > fetch c_cust_id into val;
>> > end if;
>> > close c_cust_id;
>> >
>> > if mod(val,2) = 1 then
>> > raise cust_id_not_even;
>> > end if;
>> >exception
>> > when cust_id_not_even then
>> > dbms_output.put_line('Critical Error: blah blah');
>> >end;
>> >/
>>
>>
>> that second fetch ain't fetching anything. If you coded:
>>
>> fetch c_cust_id into val;
>> if ( c_cust_id%NOTFOUND ) then
>> raise_application_error( -20001, 'bummer' );
>> end if;
>>
>> you would see that. You need to follow an explicit fetch by that check EVERY
>> TIME.
>>
>>
>> you should code:
>>
>>
>> set serveroutput on;
>> declare
>> val number := 0;
>> cust_id_not_even exception;
>> begin
>> select seq_customer_id.nextval INTO val from dual;
>>
>> if mod(val,2) = 1 then
>> select seq_customer_id.nextval INTO val from dual;
>> end if;
>>
>> if mod(val,2) = 1 then
>> raise cust_id_not_even;
>> end if;
>> exception
>> when cust_id_not_even then
>> dbms_output.put_line('Critical Error: blah blah');
>> end;
>> /
>>
>>
>>
>> Or, you could just:
>>
>> create sequence seq_customer_id start with 2 increment by 2;
>>
>> that'll ALWAYS be even and you can lose that bit of logic alltogether.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jun 23 2001 - 09:29:46 CDT