On 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.
October 3rd, 2006 at 10:07 am
lmsyhmuda…
oslfenrksde uhftenctby ymgyhascvo urmyumvgia …
January 6th, 2007 at 5:43 am
I see we share a common interest! Excellent job on the website!
April 10th, 2007 at 2:03 am
Duel 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?…
April 13th, 2007 at 6:27 pm
Twingle,
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_statusfrom 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 OPEN
Hope this helps.
August 24th, 2007 at 11:27 am
I 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.
November 23rd, 2007 at 8:57 pm
Some 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
January 10th, 2008 at 11:02 am
Along 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
January 20th, 2008 at 10:10 am
hi
its a beautiful sight,my question is iam haveing a column named sex
in 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.
July 21st, 2008 at 12:42 pm
Select 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’;