its for dual table (more then one row)
Even Oracle 10gR1 and above always makes sure that DUAL returns one and only one row even if it contains more than one row.
still you can get below error for more rows in dual table.
SQL> drop table drop_test ;
drop table drop_test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
(note:- don't play with dual table in production database ever)
examples ;-
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 20 10:19:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@orcl105 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select sysdate from dual ;
SYSDATE
---------
20-NOV-11
SQL>
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL>
SQL> insert into dual values ('H') ;
1 row created.
SQL> commit ;
Commit complete.
SQL>
SQL> create table dual_two_row as select * from dual ;
Table created.
SQL>
SQL>
SQL> select * from dual_two_row ;
D
-
X
H
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL> create table drop_test (id number) ;
Table created.
SQL>
SQL> insert into drop_test values(5);
1 row created.
SQL> commit ;
Commit complete.
SQL>
SQL> drop table drop_test ;
drop table drop_test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL>
SQL>
SQL> select * from dual ;
D
-
X
SQL> drop table dual_two_row ;
drop table dual_two_row
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL> select * from dual ;
D
-
X
SQL> create table dual_new as select * from dual ;
Table created.
SQL>
SQL> select * from dual_new ;
D
-
X
H
SQL> delete from dual where dummy='H' ;
1 row deleted.
SQL> commit ;
Commit complete.
SQL>
SQL> drop table dual_two_row ;
Table dropped.
SQL>
SQL> drop table drop_test ;
Table dropped.
SQL>
SQL> drop table dual_new ;
Table dropped.
SQL>
SQL>
SQL> select * from dual ;
D
-
X
SQL>
SQL>
SQL>
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL>
see more about dual table ...
