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@(protected)] Sent: Tuesday, November 08, 2005 3:46 PM To: Rudy Zung Cc: frank4oraclel@(protected); ORACLE-L; frank.hansen@(protected) Subject: Re: Select from dual return 3 rows !
On 11/8/05, Rudy Zung <Rudy.Zung@(protected)> 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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Message</TITLE> <META http-equiv=Content-Type content="text/html; charset=us-ascii"> <META content="MSHTML 6.00.2800.1479" name=GENERATOR></HEAD> <BODY> <DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2>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.)</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Logged in as SYS; select from DUAL, got 'X'.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Insert into DUAL values ('A'); select from DUAL, got 'X'.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Insert into DUAL values ('B'); select from DUAL, got 'X'.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Delete from DUAL; select from DUAL, got 'A'. (this is kind of creepy for me.)</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Delete from DUAL; select from DUAL, got 'B'.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Delete from DUAL; select from DUAL, got nothing.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2>Fearing developers banging down the non-existent door of my cubicle if this transaction got committed: rollback.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2 >Select from DUAL, got back original 'X'.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2>This was on 10.1.0.2.</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2>...Rudy</FONT></SPAN></DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=405250321-08112005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <BLOCKQUOTE style="MARGIN-RIGHT: 0px"> <DIV></DIV> <DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma size=2>-- --Original Message-- --<BR><B>From:</B> Jared Still [mailto:jkstill@(protected)] <BR><B>Sent:</B> Tuesday, November 08, 2005 3:46 PM<BR><B>To:</B> Rudy Zung<BR><B>Cc:</B> frank4oraclel@(protected); ORACLE-L; frank.hansen@(protected)<BR><B>Subject:</B> Re: Select from dual return 3 rows !<BR><BR></FONT></DIV>On 11/8/05, <B class=gmail_sendername >Rudy Zung</B> <<A href="mailto:Rudy.Zung@(protected)">Rudy.Zung@(protected)</A>> wrote: <DIV><SPAN class=gmail_quote></SPAN> <BLOCKQUOTE class=gmail_quote style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204 ,204) 1px solid"> <DIV><SPAN><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV> <DIV><SPAN><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV></BLOCKQUOTE></DIV><BR>That is version dependent.<BR><BR>At least as far back as <A href="http://8.1.7.4">8.1.7.4</A>, there can<BR>be only one row in sys.dual.<BR><BR>Try it.<BR><BR clear=all><BR>-- <BR>Jared Still<BR>Certifiable Oracle DBA and Part Time Perl Evangelist<BR></BLOCKQUOTE></BODY></HTML>
