DUAL Table In Oracle

Oracle database contains numerous standard functions such as SYSDATE, MAX, and SUM etc. One cannot execute these in-built Oracle SQL functions directly. To use them, a standard table is available in Oracle database called DUAL. Charles Weiss created DUAL table in Oracle data dictionary initially with two rows which was reduced to one row thereafter.

DUAL table is located in SYS schema of Oracle database. It comes by default with every versions of Oracle database.

Structure of DUAL table

The DUAL table contains one column called DUMMY of length 1 and data type VARCHAR2 with value X in it.

dual table

Operations of DUAL table

  • Static values can be derived using DUAL table.
SELECT 1 FROM DUAL;

Output
---------
1
  • Pseudocolumns can be used in DUAL table.
SELECT SYSDATE FROM DUAL;

Output
---------
25/06/2018 11:55:37
  • DUAL table executes standard SQL function.
SELECT GREATEST(1,2) FROM DUAL;
Output
---------
2
  • DUAL table performs mathematical operations.

SELECT 1+1 FROM DUAL;

Output
---------
2
  • The DUAL table derives value of user defined function.

SELECT SUM_SALARY(100, 100) as Salary FROM DUAL;

Output
---------
200
Here SUM_SALARY is user defined function with two parameters.
  • DUAL table derives user information of database.
SELECT USER FROM DUAL;

Output
---------
apps

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.