Re: Error with sequence

"vlad" <bulk_at_sfatcu.com> schreef in bericht 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
>
>

My test, using your script:

OID> conn peter/peter
Connected.

OID> DROP SEQUENCE seq;
CREATE SEQUENCE seq;
DECLARE
x int;
DROP SEQUENCE seq
*
ERROR at line 1:
ORA-02289: sequence does not exist

OID>
Sequence created.
OID> OID> 2 3 BEGIN
4 SELECT seq.nextval INTO x FROM DUAL;
5 END;
6 /
SELECT seq.currval FROM dual;
PL/SQL procedure successfully completed. OID> ...
CURRVAL

Seems to be a local problem! (I'm using 9.2.0.2 on HP-UX)

Peter Received on Mon Jan 27 2003 - 06:21:49 CST