GeeKs Blogging @ dotCOM

ORACLE – DUAL Table

by Nitin Jain

---

One of the first things that any introductory SQL tutorial teaches, is about the DUAL table in Oracle. It is the commonest of the lot, and extremely useful, I must say. The DUAL table is installed automatically with the standard Oracle Data Dictionary at the time of installing the ORACLE Database itself.

I wanted to write an introductory article on Siebel EIM today, however,somehow, DUAL seemed to catch a bigger fantasy! In any case, proficiency in DB is just as much desired at times, as in Siebel Architecture. I tried a little dabbling around with this little table. I am summing up my findings here.

As I said before, the DUAL table is installed along with the Oracle Data Dictionary

After logging into Oracle, I described the table. I was using Oracle’s SQL Developer, so if you were expecting the SQL prompt here, I am sorry!

DESC DUAL

Output:

desc dual
Name                     Null?    Type
—————————— ——– ———————————————————————————————–
DUMMY                             VARCHAR2(1)

1 rows selected

Next statement, “SELECT *”..

SELECT * FROM DUAL

Output:

DUMMY
—————-
X

I tried the above from my own login without any Admin priviledges on the Oracle DB and it seemed to work just as fine.
DUAL table can be very useful with the many uses that it ca inherently be put to.

Finding out the System Date:

SELECT SYSDATE FROM DUAL

Output:

SYSDATE
—————–
10-AUG-09

A number of system variables may be found out from the system this way, using the DUAL Table.

It can also be useful, if in case you want to output a particualar value in the output of an SQL Statement. For example:

SELECT ‘GEEKSBLOGGINGAT.COM’ FROM DUAL

Output:

‘GEEKSBLOGGINGAT.COM’
————————————
GEEKSBLOGGINGAT.COM

The above functionalities are of immense use, especially in the case of Siebel EIM where we need to twist the outputs as per our Business Requirements. One of the places it can be used is to set up the Headers to the SQL Output, while spooling it to a TXT file.

Now, the catch. Try doing the above on other tables, it will still work. Surprised?? However, we generally use DUAL in normal practice so as not to touch the other tables and tamper them by mistake. Also, DUAL will return exactly one record, while the other tables are likely to return much larger number of records, which could possibly keep varying with time.

There is obviously much more to the DUAL table. It is already available in detail on the Oracle website for the more inquisitive. The above was only a brief introduction.

Related posts:

1. Oracle Siebel Certification needs hands on training – II Most of Oracle Certifications need a mandatory hands on training....
2. Oracle 11g Certification coming soon Good news for all the Oracle 11g Database Administrator Certification...
3. Changes to the Oracle Homepage? Just browsing through the Internet, particularly the Oracle Homepage, http://www.oracle.com,...
4. Oracle Fusion Middleware 11g released Hi, We had shared the most happening news of the...
5. Oracle buys IP Assets of Conformia Oracle recently announced that it has acquired the Intellectual Property...
6. ORACLE announces new release of Coherence Now, this is going to come as good news to...
7. Sun + Oracle is faster: Proof October 14 Well, here’s the second one in series for all the...