Insert, Delete, Truncate Operations with Oracle Dual Table. Just Not Fun

advertisements

_____________________________________________________________________________________________________________________

Don’t play with dual table in an active database. Really it is not fun.

Insert Row in Dual

If you insert a row into dual, it will not list in the select command. But if you try to create a dummy table from the dual it will show the actual content of the dual table.

Example

SQL> insert into dual values ('Y');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D

-

X

Creating a dummy table from the dual and which contains two rows.

SQL> create table dummy_dual as select * from dual;

Table created.

SQL> select * from dummy_dual;

D

-

X

Y

If you insert more than one row into the dual table to the database prior to 11g version, the drop table command will show the following error.

drop table dummy_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

Truncate  and Delete operations on Dual

Delete command cannot delete the row from the dual only truncate command can delete the row from dual.

Example

SQL> select * from dual;

D

-

X

SQL> delete from dual;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dual;

D

-

X

SQL> create table dummy_dual as select * from dual;

Table created.

SQL> select * from dummy_dual;

D

-

X

SQL> select * from dual;

D

-

X

SQL> truncate table dual;

Table truncated.

SQL> select * from dual;

no rows selected

SQL> insert into dual values ('X');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D

-

X

_____________________________________________________________________________________________________________________

Website Stats