Re: Error with sequence

From: Allan <allan_at_nospam.com>
Date: Mon, 27 Jan 2003 14:34:32 +0200
Message-ID: <b13913$1v60$1@nnrp01.ops.uunet.co.za>

Try do a "select * from dual" you might have more than one row inserted. I've seen this before when the data dictionary create scripts were run twice.

Allan

"Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message news:v3a9751u0dli74_at_corp.supernews.com...
> "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
> ----------
> 1

>

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

>

> Peter

>
> Received on Mon Jan 27 2003 - 06:34:32 CST