Wednesday, April 20, 2011

The Oracle DUAL table

dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

rene@ora92> desc dual
Name Null? Type
----------------------- -------- ----------------
DUMMY VARCHAR2(1)

rene@ora92> select * from dual;

D
-
X

The owner of dual is SYS but dual can be accessed by every user.
As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefor, dual is the prefered table to select a pseudo column (such as sysdate

select sysdate from dual

Although it is possible to delete the one record, or insert additional records, one really should not do that!.
-------------------------
Note that the outer query references the dummy table called “dual” the dual table is used in Oracle when you need to run SQL that does not logically have a table name. For example, we can select our current user ID from dual and the current date from dual.


select user from dual;

USER
------------------------------
PUBS

select sysdate from dual;

SYSDATE
---------
27-MAY-02


We can also use the dual table for date arithmetic. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c: directory, add your birth date, and run the query see what day of the week you were born on.


select
to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day')
from dual;

TO_CHAR(T
---------
sunday

No comments:

Post a Comment