Wow! I made DUAL work like a FIFO queue! Oracle's definitely done some fancy
dancing on DUAL in the recent versions (per Jared's comments that
single-row-only DUAL going back to 817.)
Logged in as SYS; select from DUAL, got 'X'.
Insert into DUAL values ('A'); select from DUAL, got 'X'.
Insert into DUAL values ('B'); select from DUAL, got 'X'.
Delete from DUAL; select from DUAL, got 'A'. (this is kind of creepy for me.)
Delete from DUAL; select from DUAL, got 'B'.
Delete from DUAL; select from DUAL, got nothing.
Fearing developers banging down the non-existent door of my cubicle if this
transaction got committed: rollback.
Select from DUAL, got back original 'X'.
This was on 10.1.0.2.
...Rudy
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Tuesday, November 08, 2005 3:46 PM
To: Rudy Zung
Cc: frank4oraclel@xxxxxxxx; ORACLE-L;
frank.hansen@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Select from dual return 3 rows !
On 11/8/05, Rudy Zung <Rudy.Zung@xxxxxxx> wrote:
DUAL, when you really come down to it, is really just a table
into which Oracle defaults with just a single record. However, because it is
just a simple table, additional records can be inserted into it, as you have
just discovered.
That is version dependent.
At least as far back as 8.1.7.4, there can
be only one row in sys.dual.
Try it.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist