oracle dual table

In Oracle, the ‘select’ statement must have a ‘from’ clause. However, some queries don’t require any table. They just perform some operation or calculation based on some data that is irrelevant to the table. for example,

SELECT UPPER(‘hello world’) from some_table;

SELECT 8888*8888 from some_table;

This is where the DUAL table comes in hand, which can be used to replace ‘some_table’. The DUAL table is a special table provided by Oracle, which belongs to the schema of the user SYS, but is accessible to all users. The DUAL table has one column named ‘DUMMY’ with data type ‘varchar2’ and contains only one row with the value ‘X’.

Using the DUAL table, the ‘select’ statements above can be replaced with the below statements, which removes the user-created useless table named ‘some_table’.

SELECT UPPER(‘hello world’) from dual;

SELECT 8888*8888 from dual;

Another use case for DUAL table is to check the current sequence number of a column in a table. Unlike mysql or mariadb, which maintains the sequence number internally in the database, Oracle database delegates the sequence number generation to the user. In order to generate a sequence number for a column, the user must first creates a sequence object, then use the sequence object to generate the next sequence number. for example,

CREATE SEQUENCE book_id_seq;
INSERT INTO books VALUES(book_id_seq.nextvl, ‘hello world’)

The user can inspect the current sequence number as below.

SELECT book_id_seq.currval from dual;