Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Error with sequence
How many rows in dual ?
Do a select * from dual and post the results please.
"vlad" <bulk_at_sfatcu.com> wrote in message
news:cr9Z9.50285$VU6.44823_at_rwcrnsc52.ops.asp.att.net...
> I've got this simple script:
>
> ----------------------------------------------------------------
> DROP SEQUENCE seq;
> CREATE SEQUENCE seq;
>
> DECLARE
> x int;
> BEGIN
> SELECT seq.nextval INTO x FROM DUAL;
> END;
> /
> SELECT seq.currval FROM dual;
> ----------------------------------------------------------------
>
>
> This is the output from SQL*Plus.
>
> ----------------------------------------------------------------
> Sequence dropped.
>
> Sequence created.
>
> DECLARE
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at line 4
>
> CURRVAL
> ----------------
> 2
> ----------------------------------------------------------------
>
>
> The problem seems to be that the sequence is generating a result set with
> two numbers instead of one. If I add "WHERE rownum = 1" to the SELECT
> statement inside the trigger, everything works fine.
>
> I already posted an error I'm getting when trying to use DBMS_SPACE
> routines. The error message is exactly the same. Is something wrong with
the
> sequences in my database? I'm using Oracle9i Enterprise Edition Release
> 9.2.0.1.0. I looked inside the Oracle manuals, and I see them using in
> triggers "SELECT sequence.nextval INTO variable FROM DUAL" without using
> "WHERE rownum = 1".
>
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg
> .htm#1051
>
>
> vlad
>
>
>
>
>
Received on Mon Jan 27 2003 - 08:35:21 CST