Error with sequence

From: vlad <bulk_at_sfatcu.com>
Date: Mon, 27 Jan 2003 12:17:12 GMT
Message-ID: <cr9Z9.50285$VU6.44823@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 - 06:17:12 CST