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