pseudo columns & DUAL table - what do they actually mean?

Question

Dual table is used to select pseudo columns. it has one row and one column DUMMY which has a value X.

I have two questions

1. What actually does a pseudo column mean?

2. How is the dual able to give the value for example:

   select sysdate from dual
   

   will result in current datetime. How is this possible?

Answer 1

A pseudo-column is a function 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.

The nomenclature dates from the earlier days of Oracle, before we had PL/SQL. It just means that we can use these functions in the projection of a SELECT statement, just like the columns of a table. Nowadays we can write our own functions and use them in SQL statements without blinking, and so the phrase "pseudo-column" is a tad confusing.

The feature which distinguishes a function from a pseudo-column is that the pseudo-column returns a different value for each row in the resultset whereas a function returns the same value (unless some column in the table is passed as a parameter to derive the value).

Dual is another venerable slice of Oracle history. It is a table which contains one row, and which the database knows contains one row. So the select statement you quote is just saying "give me the current datetime". It is functionally equivalent to

select sysdate 
from emp
where rownum = 1
/

In PL/SQL the select from dual is nugatory. We can just code this:

l_date := sysdate;

One common use for DUAL used to be getting the next value of a sequence in a trigger. Since 11g we can do ...

:new.id := my_seq.nextval;

Under the covers this still executes select my_seq.nextval into :new.id from dual;