Explaining the Oracle dual Table!

When working with the Oracle database, the dual table is an entity you will almost certainly encounter. While its name might sound insignificant, it actually has extremely convenient and profound characteristics. This article covers everything a beginner needs to fully understand the dual table, from its basics and practical examples to important precautions for avoiding trouble.

What is the dual Table?
1. Why is the dual Table Necessary?
2. Let’s Check the Structure of the dual Table
Basic Usage of the dual Table
Advanced Techniques for the dual Table
Cautions: Common Misconceptions About the dual Table
1. 1. Do Not Add Multiple Rows
2. 2. Do Not Drop the Table
Conceptual Diagram of the dual Table’s Operation
Summary of Common Query Examples
Summary

What is the dual Table?

The dual table is a default table provided by Oracle. It is a simple test table used to retrieve the results of calculations or functions.

Rows: Always 1 row
Columns: 1 column (the DUMMY column)
Content: The DUMMY column contains the value X.

Why is the dual Table Necessary?

In Oracle, when retrieving data using a SELECT statement, you normally specify a table. However, the dual table is not a typical table; it is a “special work table” provided by Oracle. This makes it possible to easily retrieve the results of functions or operations, even without referencing any table data.

Let’s Check the Structure of the dual Table

First, let’s check the basic information of the dual table.

DESC dual;

Result:

SQL> DESC dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

The dual table consists of only one column named DUMMY.

You can also check its contents directly with the following query.

SELECT * FROM dual;

Result:

SQL> SELECT * FROM dual;

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

Basic Usage of the dual Table

The dual table is utilized in the following scenarios within an Oracle database.

1. Testing Arithmetic Operations

You can instantly check simple calculations using the dual table.

SELECT 3 + 5 AS sum FROM dual;

SQL> SELECT 3 + 5 AS sum FROM dual;

       SUM
----------
         8

2. String Manipulation

You can test string concatenation and functions.

SELECT 'Hello' || ' Oracle!' AS message FROM dual;

SQL> SELECT 'Hello' || ' Oracle!' AS message FROM dual;

MESSAGE
-------------
Hello Oracle!

3. Retrieving the Date or Time

You can retrieve the current date and time or system time.

SELECT SYSDATE AS current_date FROM dual;
-- Result: Current date

To retrieve the time with more precision, use SYSTIMESTAMP.

SELECT SYSTIMESTAMP FROM dual;

4. Outputting a Constant

The dual table is also used to return a fixed value from a query.

SELECT 42 AS answer FROM dual;
-- Result: 42

5. Retrieving the System Username

You can check the user for the current database connection.

SELECT USER AS current_user FROM dual;
-- Result: Connected username

Advanced Techniques for the dual Table

The dual table can be used for more advanced purposes beyond simple queries.

1. Retrieving a Sequence Value

It is used to get the next value from a sequence.

SELECT my_sequence.NEXTVAL AS next_value FROM dual;

2. Generating Dummy Data

To generate multiple rows of dummy data, combine it with the CONNECT BY clause.

SELECT LEVEL AS num FROM dual CONNECT BY LEVEL <= 10;

SQL> SELECT LEVEL AS num FROM dual CONNECT BY LEVEL <= 10;

       NUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

3. Testing Conditional Logic (CASE)

It can also be used to return different values based on a condition.

SELECT CASE WHEN 1 = 1 THEN 'True' ELSE 'False' END AS result FROM dual;

SQL> SELECT CASE WHEN 1 = 1 THEN 'True' ELSE 'False' END AS result FROM dual;

RESULT
----------
True

Cautions: Common Misconceptions About the dual Table

1. Do Not Add Multiple Rows

If you add multiple rows to the dual table, query results may not be as expected.

INSERT INTO dual (dummy) VALUES ('Y');
SELECT SYSDATE FROM dual;
-- Multiple rows may be returned

2. Do Not Drop the Table

The dual table is a critical part of the Oracle system. Dropping it will impact the entire database.

DROP TABLE dual;
-- Do not execute!

Conceptual Diagram of the dual Table’s Operation

(※ In a diagram, the dual table would be depicted as a “window” that returns the results of functions or expressions.)

Thus, the dual table is utilized as a lightweight mechanism for returning results.

Summary of Common Query Examples

Purpose	Query Example	Example Result
Get the current date	`SELECT SYSDATE FROM dual;`	Current date
Concatenate strings	`SELECT ‘Hello’‘ Oracle!’ FROM dual;`	Hello Oracle!
Perform arithmetic	`SELECT 10 / 2 FROM dual;`	5
Get next sequence value	`SELECT my_sequence.NEXTVAL FROM dual;`	Sequence value

Summary

The dual table plays a simple yet crucial role in the Oracle database. Its applications are diverse, and its appeal is that even beginners can learn to use it quickly.

What We Learned in This Article:

The basic structure and uses of the dual table
Practical usage and advanced techniques
Points for avoiding trouble

Let’s master the dual table to make your Oracle database operations even smoother!

[reference]
Oracle Database SQL Language Reference, 19c