Mystery solved!!!

Sometimes posting to a newsgroup has the wonderful effect of making me really think about the problem and giving me great ideas. Well, guess what. One day I decided to see what happens if I insert a row into DUAL. Well, I looked like nothing happened. But DUAL is a very strange table. Here's some output from SQL*Plus:

04:21:31 SQL> select * from dual;

D
-
X

04:21:39 SQL> select count(*) from dual;

        COUNT(*)

               2

04:21:45 SQL> delete from dual;

1 row deleted.

04:21:50 SQL> select count(*) from dual;

        COUNT(*)

               1

So, I really had two rows in DUAL, but a SELECT shows only one. Even weirder, a DELETE deleted only the row that I added. Very, very funny :)

vlad

"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 - 06:29:13 CST