RE: Select from dual return 3 rows !

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 9 Nov 2005 17:03:05 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621148C19E2@irvmbxw02>

>De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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    

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 09 2005 - 19:05:14 CST