Dual Table in Oracle and its uses in Database #Diksha Oracle Training Centre

 

Dual Table in Oracle and its uses in Database.

Hello Everyone,

Today we will discuss about Dual table in Oracle and it’s benefit in Database.Dual table in Oracle which is a special and dynamic table used for evaluating expressions or calling functions. Once you Install Oracle Database in your System Dual table is available in all schema’s . The DUAL table contains one column named DUMMY whose data type is VARCHAR2() and contains one row with a value X. The DUAL table is most simple one because it was designed for fast access and can be used for any calculation checking date and printing any value as the output for our requirement. To Access Dual table we can use Select statement.

To see the output from Dual table we can use below select statement and can see output as column DUMMY with one Row value X.

Select * 

from dual;

By using the DUAL table, we can execute queries that contain functions that do not involve any table like the UPPER() function as below:

SELECT

  UPPER('Welcome to Diksha Oracle Training Centre')

FROM

  dual;

Output of above query in Oracle compiler is as below.

 

From dual table we can  can use expressions in the SELECT clause of a query that accesses the DUAL table. Below are the select statement for calculation of any number from the dual table.

 

Select

( 20+30/2)

Calculation

From Dual;

 

Output of above query in Oracle compiler is as below.

From Dual table we can check the current date with date format for Oracle and also we can manipulate date format as per our requirement.

Seeing the current date and it’s format we can use below query.

Select

Sysdate

From dual;

Changing date format from default we can use below query :

Note : Date format we can change as per our requirement in different format such  as 'DD-MON-YY', 'DD-MM-YY', 'YYYY-MM-DD'  etc as per our need.

select

TO_CHAR(sysdate ,'DD-MON-YY')

from

dual;

Output of above query is as below :

 

Below SQL code display the numbers 1 to 8 from DUAL.

 

SELECT level

FROM DUAL

CONNECT BY level <=8;

 

Output of above Query is as below in Oracle Compiler.

 

Note :Owner of DUAL table is SYS which owns the data dictionary, therefore DUAL is part of the data dictionary so DUAL Table can be accessed by every user exist in the Oracle database. Please explore Dual table as much as you can for calculation and other activities as per your requirement.

Oracle treats the use of DUAL the same as calling a function which simply evaluates the expression used in the select list. Optimization provides even better performance than directly accessing the physical DUAL table. Dual table plays very important role in Oracle database as this table can be used for multipurpose for data calculation and other activities in SQL as well as PL/SQL block.

 

Please go through this blog carefully and let me know if you have any questions as dual table in Oracle is very important and this blog is important for fresher candidate who is preparing for interview as an Oracle SQL and PL/SQL developer as some questions asked from this topic as well during interview.

Thanks.