Dual in Oracle

Dual in Oracle

For Oracle dual, we need to clarify the following points:

First point
Dual is not a acronym but a complete word. Dual refers to logarithm, Which is dual and dual when used as an adjective.

Second point
The dual table in Oracle is a single-row, single-column virtual table.

Third
A Dual table is a table automatically created by oracle and the data dictionary. The table has only one DUMMY column, the data type is VERCHAR2 (1), and the dual table has only one 'X' data ', oracle has internal logic to ensure that there is always only one data record in the dual table.

Point 4
Dual tables are mainly used to select system variables or evaluate the value of an expression.

Fifth point
The most common simple example is SELECT sysdate FROM daul.
Oracle's SELECT syntax is limited to SELECT * | [column1 [AS alias1], column2 [AS alias2] FROM table
Therefore, there is no way to query without a table name, and the time and date are not stored in any table, so the concept of this dual virtual table is introduced.

Use of DUAL tables

Dual is an existing table in Oracle that can be read by any user. It is often used in Select statement blocks without a target table.

View connected users
Select user from dual;
SYSTEM

View current date and time
Select sysdate from dual;
2012-1-24 1

Date Conversion
Select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;
2012-01-24 15:02:47

Used as a calculator
Select 1 + 2 from dual;
3

Create and view sequence values
Create sequence aaa increment by 1 start with 1;
Select aaa. nextval from dual;
1

Select aaa. currval from dual;
1

DUAL is a table with one row and one column. If you execute insert, delete, and truncate operations in the table, many programs may fail. The results also vary with SQL * plus, pl/SQL dev, and other tools. If we want to insert dual into a piece of data, what will happen?

Insert into dual values ('y ');

Select * from dual;
DUMMY
-----
X
Y

Select sysdate from dual;
SYSDATE
-----------
2012-07-15
2012-07-15

At this time, two records are returned, which may cause problems. ORACLE throws a TOO_MANY_ROWS (ORA-01422) exception in stored procedures that obtain time or other information by using select sysdate into v_sysdate from dual.

Therefore, it is necessary to ensure that there is only one record in the DUAL table and the UPDATE, INSERT, and DELETE permissions of the DUAL table cannot be released at will, which is very dangerous to the system.

If you accidentally delete the dual, it can be restored.