Oracle’s Dual Table
Categories: OracleOn my article about auto increment columns in Oracle Stephane asked about the use of dual in Oracle.
Dual is sort-of a dummy table. It’s a real table, but not one that should ever get updated. It exists in every Oracle database and is useful for troubleshooting and development.
If you describe dual you will see it’s definition:
SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
Dual has only one row, but you can select the value of it.
SQL> select * from dual;
D
-
X
Dual can be selected by any user. Since it contains just the one row you can use it to return a single result to you, like
SQL> select sysdate from dual;
SYSDATE
---------
11-AUG-06
Now you could have done this against any single-row table. You could even create a single row table to select results like this, but since Oracle provides the dual table everyone tends to use it.
Thanks for the question Stephane. I think many people use Dual without knowing (or even wondering) what it is.
10 Responses to “Oracle’s Dual Table”
-
hazbjxzkvy Says:
October 3rd, 2006 at 10:07 amlmsyhmuda…
oslfenrksde uhftenctby ymgyhascvo urmyumvgia …
-
jugar maquina tragamonedas gratis apuestas de casino en lnea Says:
January 6th, 2007 at 5:43 amI see we share a common interest! Excellent job on the website!
-
Twingle Says:
April 10th, 2007 at 2:03 amDuel table is having a column “DUMMY” and its of type VARCHAR2(1).How it can return sysdate,which always have size greater than VARCHAR2(1)? Anybody having an idea about this?…
-
Jon Emmons Says:
April 13th, 2007 at 6:27 pmTwingle,
Sorry for the delayed response.
You can actually select all sorts of things from any table. Try this:
select 4+3, sysdate, 'some text' from user_tables;The values will be repeated for every record in user_tables. Basically these values aren’t selected from the table, but are rather provided as constant values, independent of the table contents.
It’s a bit tricky, but this functionality allows us to format the output like in the following (which also uses the double-pipe concatenate operator):
select 'The user ' || username || ' has a status of ' || account_status
from dba_users;'THEUSER'||USERNAME||'HASASTATUSOF'||ACCOUNT_STATUS
--------------------------------------------------------------------------------
The user MDSYS has a status of EXPIRED & LOCKED
The user ORDSYS has a status of EXPIRED & LOCKED
The user EXFSYS has a status of EXPIRED & LOCKED
The user DMSYS has a status of EXPIRED & LOCKED
The user DBSNMP has a status of OPEN
The user SCOTT has a status of OPENHope this helps.
-
Dov Wasserman Says:
August 24th, 2007 at 11:27 amI find it amusing that the table explicitly created for the purpose of having one row is called dual. Anyone happen to know why that name was chosen? I’m sure there’s some interesting historical etymology. Well, interesting to some of us database + word geeks.
-
Bernd Eckenfels Says:
November 23rd, 2007 at 8:57 pmSome Application Servers use the “select ‘X’ from DUAL;” dummy statement to verify the ORacle Connection is working. This used to make lots ofproblems because that table got quite “hot”. This is why Oracle 10g has a special Optimizer for this Table.
Greetings
Bernd -
Norman Says:
January 10th, 2008 at 11:02 amAlong with Dov Wasserman, author of comment #5, I wonder how the name “Dual” was selected. We might ask where the name was first used and who were the contributors. Let’s keep looking for an answer.
Good luck!
Norman
-
goutam Says:
January 20th, 2008 at 10:10 amhi
its a beautiful sight,my question is iam haveing a column named sexin which a subdivsion male and female catgeory is there and i have
some memers underit how can i retrive the male and female seperately
in a single statment.
-
ymonette Says:
July 21st, 2008 at 12:42 pmSelect sex
from (table name)
order by sex ASC;This will separate M from F
Otherwise if you want just M (or just F)
Select sex
from (table name)
where sex = ‘M’; -
lkeria Says:
June 21st, 2010 at 10:54 amYou can also use the DUAL table tu test the avaibality of the database. Select 1 from dual;
