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

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