What is DUAL ?

DUAL is a special single row table present by default in Oracle Databases. i.e. this table exists in all Oracle Databases by default

Structure of DUAL table

DUAL table has only one column called DUMMY as Varchar2(1) data type and value as X.

QL> desc dual;
Name Null? Type
----------------------------
DUMMY VARCHAR2(1)

SQL> select * from dual;
D
-
X

Examples:-

SQL> select 1 from dual;

         1
----------
         1


SQL> select 'google' as name from dual;
NAME  
------
google


SQL> select 1+2 from dual;

       1+2
----------
         3


SQL> select 5*4,5/4 from dual;

       5*4        5/4
---------- ----------
        20       1.25


SQL> select sysdate from dual;

SYSDATE  
---------
04-AUG-22


SQL> select systimestamp from dual;

SYSTIMESTAMP                          
--------------------------------------
04-AUG-22 01.59.20.096241000 AM -04:00


SQL> select user from dual;

USER                                                                            
-----
HR      


SQL> select lower('GOOGLE') as name from dual;

NAME  
------
google


SQL> select 
(select first_name from employees where employee_id = 130) as first_name from DUAL;

FIRST_NAME          
--------------------
Mozhe               

Usage of Dual

1. Find current date, time, timestamp, user etc.

SQL> select sysdate from dual;

SYSDATE  
---------
04-AUG-22


SQL> select systimestamp from dual;

SYSTIMESTAMP                          
--------------------------------------
04-AUG-22 01.59.20.096241000 AM -04:00


SQL> select user from dual;

USER                                                                            
-----
HR 

2. Test oracle functions (both in-built and user defined)

SQL> select lower('GOOGLE') as name from dual;

NAME  
------
google


SQL> create or replace function func_test(l_employee_id varchar2) 
     return varchar2
     as
     ename varchar2(50);
     begin
     select first_name into ename from employees where employee_id = 
     l_employee_id;
     return ename;
     end;
    /

Function created.

SQL> select func_test(130) from dual;

FUNC_TEST(130)                                                                  
--------------
Mozhe 

3. Use as a calculator

SQL> select 10+2, 10-2, 10*2, 10/2 from dual;

      10+2       10-2       10*2       10/2
---------- ---------- ---------- ----------
        12          8         20          5


SQL> select exp(2) from dual;

    EXP(2)
----------
 7.3890561


SQL> select power(2,4) from dual;

POWER(2,4)
----------
        16