Playing around with my toy database I asked myself, "What happens if DUAL has more than 1 row?" I found out.
SQL> insert into dual values ('Y');
1 row created.
SQL> select * from dual;
D
-
X
SQL> select count(*) from dual;
COUNT(*) ---------- 1
I tried it again. Same result. "Oh, I guess I can't insert into DUAL", says I, and I went about my business.
Later I logged on as SCOTT and tried to drop a table. Playing I have more EMP tables than employees.
SQL> DROP TABLE EMP4;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
WHAT??!!?
Yes it is there and there is only 1 table called EMP4.
SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP4'
OWNER OBJECT_NAME
------------------------------ ------------------------------
SCOTT EMP4
I looked the matter up at orafaq. and followed the instructions.
SQL> select * from dual;
D
-
X
SQL> create table temp_dual as select * from dual;
Table created.
SQL> select * from temp_dual;
D
-
X
Y
Y
Yes, I plead guilty. I DID succeed in inserting those rows into DUAL.
SQL> delete from dual where dummy = 'Y';
1 row deleted.
Strange. It deleted 1 row even though I had put 2 in.
SQL> drop table temp_dual;
drop table temp_dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
I deleted the second excess row:
SQL> delete from dual where dummy = 'Y';
1 row deleted.
and I had a functioning database back.
SQL> DROP TABLE TMP_DUAL;
Table dropped.
and then as SCOTT
SQL> DROP TABLE EMP4;
Table dropped.
OK, I get it, Oracle consults DUAL in the drop process. And don't go messing up a database of any importance. But it is odd how the fact that I was succeeding to mess things up was hidden from me. Yes it told me that I had inserted the row, but then it didn't display it with a select. It was an interesting bit of play.
