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.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

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.