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

Silver wrote:

> 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;
> /

Call a function that returns the value from the sequence.

Daniel A. Morgan Received on Thu Jun 21 2001 - 16:27:44 CDT