TechnicalBits

     The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, 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.

     A pseudo-column is an Oracle assigned value (pseudo-field) but not stored on disk.

Pseudo columns are not actual columns in a table but they behave like columns.

     For example, you can select values from a pseudo-column. However, you cannot insert into, update, or delete from a pseudo column. Also note that pseudo-columns are allowed in SQL statements, but not in procedural statements.

    A pseudo-column is also similar to a function without arguments which returns a system generated value.SYSDATE is a function which returns the current datetime; rownum is a pseudo-column that returns the row number in a result set.

NOTE: - pseudo-columns are allowed in SQL statements, but not in procedural statements.

SQL> SELECT sysdate, systimestamp FROM dual;

How does selecting from DUAL give the system time? 

    SQL has a number of built-in functions which don't need parentheses after them to invoke them. One such function in Oracle is SYSDATE.

    Remember, if you have a table, a SELECT statement with no restriction condition (WHERE clause) normally returns one row of data for each row in the table. So, given a table:

CREATE TABLE Ex1(Dummy CHAR(10) NOT NULL);

INSERT INTO Ex1 VALUES('Abacus');

INSERT INTO Ex1 VALUES('Sedentary');

INSERT INTO Ex1 VALUES('Caucasus');

Running the SELECT statement:

SELECT Dummy FROM Ex1;

Will return 3 rows. Now, suppose I write the statement as:

SELECT 'ABC', Dummy, SYSDATE FROM Ex1;

This will also return 3 rows:

ABC, Abacus, 2010-03-03

ABC, Sedentary, 2010-03-03

ABC, Caucasus, 2010-03-03

If I omit the Dummy column:

SELECT 'ABC', SYSDATE FROM Ex1;

I get:

ABC, 2010-03-03

ABC, 2010-03-03

ABC, 2010-03-03

And if I omit the string literal:

SELECT SYSDATE FROM Ex1;

I get:

2010-03-03

2010-03-03

2010-03-03

And I delete two rows and rerun the query, I get:

DELETE FROM Ex1 WHERE Dummy > 'B';

SELECT SYSDATE FROM Ex1;

I get:

2010-03-03

Because there's just the one row of data in the table Ex1.

Nominally, I could do:

 UPDATE Ex1 SET Dummy = 'X';

 RENAME TABLE Ex1 AS Dual;

Of course, you can't do that - Oracle wouldn't let you rename your table to Dual so it could be confused with the built-in DUAL table. But conceptually, the table Ex1 with a single row in it is isomorphic with DUAL.