Subsequent call to seq_name.nextval. Please help.

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;
/ Received on Thu Jun 21 2001 - 16:13:59 CDT