Oracle DUAL Table

This tutorial aims to introduce you to the basics of the Oracle database by learning about the DUAL table.

What Is the DUAL Table in Oracle

If you are just getting started in working with Oracle database, you might have come across a query as shown below:

select length('Hello world') from dual;

Since you have not created a table called DUAL, you might wonder what this table is and where it originates.

The DUAL table in Oracle is a particular type of table automatically created by the Oracle Database engine for evaluating expressions or function calls. The database engine also initializes the data dictionary for the DUAL table.

Although the SYS users own the DUAL table, it is accessible to all users in the server simply by name reference.

Therefore, calling the select statement from DUAL will not result in an error even if you have not manually created the DUAL table.

What Is the Description of the DUAL Table?

Using the describe command, we can get the table definition and see all the columns and other objects associated with the table.

DESCRIBE dual;

Output:

Name Null? Type
----- ----- -----------
DUMMY VARCHAR2(1)

The DUAL table comprises one column with the name DUMMY defined as VARCHAR2 type.

select * from dual;

Output:

DUMMY
-----------
X

The column has one row with the value of x. Hence, any constant expression from a select statement is returned only once. You can assign more rows to the table to produce a given expression or value more than once.

As for the data type, it is entirely irrelevant to the purpose of the DUAL table. Again, the goal is to have one universal row from where you can select a value.

NOTE: The DUAL table is part of the data dictionary. Therefore, avoid making modifications to the table using PL/SQL commands. This can lead to errors or corruption of the data dictionary, which can break your instance.

What Is the Purpose of the DUAL Function

As mentioned, the DUAL table is used to compute constant expressions or functions in a given select statement.

The following are examples of DUAL table usage:

select lower('HELLO WORLD') from dual;

Result:

LOWER('HELLOWORLD')
-----------------------------------
hello world

Using the DUAL table with a constant expression

select 100/10 from dual;

Result:

100/10
-----------
10

Conclusion

In this article, we explained some basic questions you may be having regarding the DUAL table in Oracle databases.

You do not have to use the DUAL table for constant expressions or function calls. Instead, you can use anything you deem necessary. However, the Oracle optimizer is aware of the DUAL table and its purpose: it contains one column and one row.

Oracle DUAL Table

What Is the DUAL Table in Oracle

What Is the Description of the DUAL Table?

What Is the Purpose of the DUAL Function

Conclusion

About the author

John Otieno