Re: Sequence returns multiple numbers

O dear !

Try "select * from dual" .. a bit paranoid maybe, but somebody might have fouled up the system.dual table (if such a thing is possible)

cheers

Glenn Baron

claese_at_my-dejanews.com wrote in article <7guts1$9kk$1_at_nnrp1.deja.com>...
> Hi all,
>
> I have a sequence defined by:
>
> CREATE SEQUENCE PRO140_FIFO
> INCREMENT BY 1
> START WITH 1
> MINVALUE 1
> MAXVALUE 2147483647
> CYCLE
>
> When I try to fetch the next sequence number, I get not one, but TWO
values
> back:
>
> Oracle7 Workgroup Server Release 7.3.2.3.1 - Production Release
> With the distributed option
> PL/SQL Release 2.3.2.3.0 - Production
>
> SQL> select PRO140_FIFO.NEXTVAL from dual;
>
> NEXTVAL
> ---------
> 21
> 22
>
> SQL>
>
> Obviously, using this sequence in a trigger will fail:
>
> create trigger tib_pro140 before insert
> on PRO140 for each row
> begin
> select PRO140_FIFO.NEXTVAL into :new.PRO140FIFO from DUAL;
> end;
>
>
> SQL> insert into PRO140 (EVENT, KRAN_NR, KRAN_TGM) VALUES (999, 99,
'Junk');
> insert into PRO140 (EVENT, KRAN_NR, KRAN_TGM) VALUES (999, 99, 'Junk')
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at "MLS.TIB_PRO140", line 8
> ORA-04088: error during execution of trigger 'MLS.TIB_PRO140'
>
>
> SQL>
>
> Has anybody seen this behavior before? I would be *most* grateful for any
> assistance.
>
> TIA,
>
> Claes Engelin
> Prevas AB
> Gothenburg, Seden
> claes(dot)engelin(at)prevas(dot)se
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

>
Received on Fri May 07 1999 - 11:40:35 CDT