In focus

Dual Table In Oracle

In this article we will discuss about Dual table in Oracle database. The DUAL Table is a special one-row table present by default.

Abhishek Sinha Apr 26, 2016

The DUAL Table is a special one-row table present by default in all Oracle database installations. The table has a single VARCHAR2(1) column called DUMMY that has a value of "X". It is suitable for use in selecting a pseudo column such as SYSDATE or USER. Dummy table is single record table which is used when we interested to select some system function not the data.

These are the followings commands in dual table :

  • Select Two Rows from dual table - If we want to select two rows from dual table, we use decode function. 
           Syntax : SELECT decode(null,null,1,0) from dual;
  • Display the Numbers 1 to 10 from dual table - This will display the row number 1 to 10 in dual table.
            Syntax : SELECT ROWNUM FROM dual CONNECT BY ROWNUM <=10
  • Remove Special Characters in dual table - If there is any special characters in the any data or record, and we want to eliminate it than it can be remove by translate built-in function.
            Syntax : translate ('Input_string', 'what you want to replace', 'what you want to write in place replacing character')

            Example  SELECT translate ('asdfsd@#@$#$%$sdfg&;','!@#$%^&;*()_+=-`~?><:/.,',' ') FROM dual;

            Output - asdfsdsdfg
  • Command which displays the structure of DUAL table

    Syntax 
    : DESC DUAL;

  • Command which displays the content of the DUAL table
           Syntax : SELECT * FROM DUAL;

           Output - 
x
  • Command which displays the string value from the DUAL table 
           Syntax : SELECT 'ABCDEF12345' FROM DUAL

           Output - ABCDEF12345
  • Command which removes all rows from the DUAL table - This removes all the rows from dual table.
           Syntax : TRUNCATE TABLE DUAL;

           TRUNCATE removes all rows from a table. The operation cannot be rolled back.
  • Check the system date from the DUAL table
           Syntax : SELECT sysdate FROM DUAL ;

           Output
- Sysdate 21-Mar-2016

Here's a free e-book on oracle: Oracle Database Concepts 

database oracle

COMMENT USING