 | | | Select from dual return 3 rows ! | Select from dual return 3 rows ! 2005-11-09 - By Jacques Kilchoer
>De : oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] De
la part de Eric Jenkinson
>
> Meta Link note 185438.1 Select * from DUAL - Delete from DUAL behaviour
mentions
> that there is internalized code that ensures that a table scan of SYS.DUAL
only returns one row.
You can still play a prank for April Fool's in your production database by
DELETING the row in DUAL (unless you have Oracle 10, which even removes that
possibility for mischief).
In Oracle 9.2, a "select sysdate from dual" will return "no rows found" if dual
has 0 rows.
In Oracle 10.1, the "select sysdate from dual" will return one row with sysdate
, but "select sysdate, dummy from dual" will return "no rows found" if dual has
0 rows.
-- Oracle 9.2
-- delete dual row: select sysdate from dual returns 0 rows
SQL> connect sys as sysdba
Entrez le mot de passe :
Connecté.
SQL> select * from v$version ;
BANNER
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> delete from dual ;
1 ligne supprimée.
SQL> commit ;
Validation effectuée.
-- the SP2-0575 error below was caused by a statement in my login.sql file
SQL> connect jrk
Entrez le mot de passe :
SP2-0575: Fonction SQL Oracle non utilisée au niveau Entry SQL92.
Connecté.
SQL> select sysdate from dual ;
aucune ligne sélectionnée
SQL>
-- Oracle 10.1
-- delete dual row: select sysdate from dual still returns a row
-- but don't select the column DUMMY
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from v$version ;
BANNER
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> delete from dual ;
1 row deleted.
SQL> commit ;
Commit complete.
SQL> connect jrk
Enter password:
Connected.
SQL> select sysdate from dual ;
SYSDATE
-- ------
09-NOV-05
SQL> select sysdate, dummy from dual ;
no rows selected
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2769" name=GENERATOR></HEAD>
<BODY><SPAN class=314475700-10112005>
<DIV dir=ltr align=left><FONT face=Tahoma><FONT size=2><B><SPAN
class=314475700-10112005>></SPAN>De :</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>De la part de</B> Eric
Jenkinson<BR><SPAN class=314475700-10112005></SPAN>></FONT></FONT></DIV>
<DIV dir=ltr align=left></DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005>> </SPAN>Meta Link
note 185438.1 Select * from DUAL - Delete from DUAL behaviour mentions</DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005>></SPAN> that
there is internalized code that ensures that a table scan of SYS.DUAL only
returns one row. </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV dir=ltr align=left>You can still play a prank for April Fool's in your
production database by DELETING the row in DUAL<SPAN class=314475700-10112005>
(unless you have Oracle 10, which even removes that possibility for
mischief)</SPAN>.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005>In Oracle 9.2, a "select
sysdate from dual" will return "no rows found" if dual has 0 rows.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005>In Oracle 10.1, the
"select sysdate from dual" will return one row with sysdate, but "select
sysdate, dummy from dual" will return "no rows found" if dual has 0
rows.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005><FONT face="Courier New"
size=2>-- Oracle 9.2<BR>-- delete dual row: select sysdate from dual returns 0
rows</FONT></SPAN></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005><FONT face="Courier New"
size=2>SQL> connect sys as sysdba<BR>Entrez le mot de passe
:<BR>Connecté.<BR>SQL> select * from v$version
;<BR>BANNER<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
<BR>Oracle9i
Enterprise Edition Release 9.2.0.4.0 - Production<BR>PL/SQL Release 9.2.0.4.0 -
Production<BR>CORE
9.2.0.3.0 Production<BR>TNS for 32-bit
Windows: Version 9.2.0.4.0 - Production<BR>NLSRTL Version 9.2.0.4.0 -
Production</FONT></SPAN></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005><FONT face="Courier New"
size=2>SQL> delete from dual ;<BR>1 ligne supprimée.<BR>SQL> commit
;<BR>Validation effectuée.</FONT></SPAN></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><SPAN class=314475700-10112005><FONT face="Courier New" size=2>-- the
SP2-0575 error below was caused by a statement in my login.sql
file</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=314475700-10112005><FONT face="Courier New"
size=2>SQL> connect jrk<BR>Entrez le mot de passe :<BR>SP2-0575: Fonction
SQL
Oracle non utilisée au niveau Entry SQL92.<BR>Connecté.<BR>SQL> select
sysdate from dual ;<BR>aucune ligne sélectionnée<BR>SQL></FONT></SPAN></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV><SPAN
class=314475700-10112005>
<DIV dir=ltr align=left><BR><FONT face="Courier New" size=2>-- Oracle 10.1<BR>-
-
delete dual row: select sysdate from dual still returns a row<BR>-- but
don't select the column DUMMY</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV dir=ltr align=left><FONT face="Courier New" size=2>SQL> connect sys as
sysdba<BR>Enter password: <BR>Connected.<BR>SQL> select * from v$version
;<BR>BANNER<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
<BR>Oracle
Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi<BR>PL/SQL Release
10.1.0.2.0 - Production<BR>CORE
10.1.0.2.0 Production<BR>TNS for Solaris: Version
10.1.0.2.0 - Production<BR>NLSRTL Version 10.1.0.2.0 - Production</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV dir=ltr align=left><FONT face="Courier New" size=2>SQL> delete from
dual
;<BR>1 row deleted.<BR>SQL> commit ;<BR>Commit complete.</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV dir=ltr align=left><FONT face="Courier New" size=2>SQL> connect
jrk<BR>Enter password: <BR>Connected.<BR>SQL> select sysdate from dual
;<BR>SYSDATE<BR>-- ------<BR>09-NOV-05<BR>SQL> select sysdate, dummy from
dual ;<BR>no rows selected</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV></BODY></HTML>
|
|
|