RE: Select from dual return 3 rows !
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Nov 2005 17:03:05 -0800
>De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
