Re: Error with sequence

"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
>
>
>
>

You are selecting from a table (whichever table DUAL resolves to) which has two rows.

You will doubtless find that some joker has either added a second row to SYS.DUAL, or has created a table in your schema, or created another table elswhere and/or messed around with synonyms.

Try select count(*) from sys.dual for starters. If the answer is 2, there's the problem.

I suggest you fix whatever is the issue, *then review your security*. Who is allowed to mess about with SYS tables? And who is allowed CREATE ANY SYNONYM or CREATE ANY TABLE or CREATE PUBLIC SYNONYM?

Regards,
Paul Received on Tue Jan 28 2003 - 13:33:14 CST