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

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.

• 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.
Received on Fri Jun 22 2001 - 21:03:03 CDT