Using DUAL table in Oracle

DUAL is a special table found in some of the databases e.g Oracle, Informix and DB2 etc. MySQL, Microsoft SQL (MS SQL) does not have this table moreover MySQL allows DUAL to be specified as a table in queries that do not need data from any tables.In oracle, this DUAL table is having one row and one column by default. The owner of DUAL is sys, can be accessed by all user. The table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.This table was created by Charles Weiss of Oracle corporation to provide a table for joining in internal views. We mostly uses this to find the calculations or checking system variables etc.

Examples

1. The following query displays the string value from the DUAL table

SELECT ‘Test String’ FROM DUAL;

Output:

Test String
———–
Test String

2. The following query displays the numeric value from the DUAL table

SELECT 123.45 FROM DUAL;

Output:

123.45
———-
123.45

3. The following query tries to delete all rows from the DUAL table

DELETE FROM DUAL;

Output:

DELETE FROM DUAL
*
ERROR at line 1:
ORA-01031: insufficient privileges

4. The following command tries to remove all rows from the DUAL table

TRUNCATE TABLE DUAL; (operation cannot be rolled back)

Output:

TRUNCATE TABLE DUAL
*
ERROR at line 1:
ORA-00942: table or view does not exist

5. You can also check the system date from the DUAL table using the following statement

SELECT sysdate FROM DUAL ;

Output:

SYSDATE
———
28-DEC-18

6. You can also check the arithmetic calculation from the DUAL table using the following statement

SELECT 5+2-2*2/2 FROM DUAL;

Output:

5+2-2*2/2
———–
6

7. Following query display the numbers 1..10 from DUAL

SELECT val
FROM DUAL
CONNECT BY val <=10;

Output:

VAL
———-
1
2
3
4
5
6
7
8
9
10

8. As DUAL is special one row one column table. For Oracle, it is useful because Oracle doesn’t allow statements like

SELECT 2+5-2*3/3;

Output:

SELECT 2+5-2*3/3
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

One thought on “Using DUAL table in Oracle

  1. Fashion Styles says:

    Thanks for your submission. I would like to say this that the first thing you will need to accomplish is to see if you really need credit repair. To do that you have got to get your hands on a copy of your credit rating. That should really not be difficult, since the government necessitates that you are allowed to get one no cost copy of your real credit report every year. You just have to request that from the right folks. You can either browse the website with the Federal Trade Commission or even contact one of the leading credit agencies right away.

Leave a Reply

Your email address will not be published. Required fields are marked *