Thread: Dual Table in Oracle

What does a dual table in oracle do? Where will this table be use at?

Originally Posted by pat.mclean

Dual table is like your scratch-pad. you can try different queries & functions using Dual.
For. E.g. if you want to find out max of any numbers that are not present in your schema & you want to do it, you can use dual this way : select max(12,54,2,75,142) from dual;

when you are using some object which doesn't have any phisical reference to your database tables, i.e. it is not resided in any table, you can refer it to dual table. This fulfils the ORACLE's requirement of source table in FROM clause.

Patrick.

Very good answer.

Thank you very much! It really was a help in answering my inquiry. Hope you will stay there for my other questions in mind.

Originally Posted by janelyn

Thank you very much! It really was a help in answering my inquiry. Hope you will stay there for my other questions in mind.

select max(12,54,2,75,142) from dual

it shows the error message invalid identifier

anybody help me....

Dual is small Oracle work table that consist of exactly 1 row and 1 column..

This table is not available in SQL Server...

Ex:

SELECT 2*2 FROM dual;

Hi,
Dual table has only one row and one column. It is a small table referenced by ORACLE. It is also reference by users to check any results.

Regards,
Norman

Hi,
Some more words to add to dual table are the one column which is present in dual table is of type char and of size 1. There are many places in which dual takes its presence. One of the place in which dual is used is sequence values are selected by querying dual.

Regards,
RyanJames

Dual table is a Table created within SYS schema having public synonym this table is created with one cloumn and one row that is very usefull to use oracle Function in plsql without having real table in your schema

E.g If you want to get user name ,sysdate of Server,or etc function you can use this function

Originally Posted by janelyn

What does a dual table in oracle do? Where will this table be use at?

The DUAL table is called "MAGIC TABLE" in Oracle ....
U can testthing with this DUAL
Ex. select * from DUAL;
O/P : X

Dual table is basically used for pseudo columns like my_seq.nextval,curval, level and also to display system date. it contains one row and one column.

DUAL table useful, for example, to generate sequence of numbers, like this:
SELECT level as ID
FROM DUAL
CONNECT BY level < 101

Will give you seqence of numbers 1..100

In your query
"SELECT level as ID
FROM DUAL
CONNECT BY level < 101"

what does 'CONNECT BY' clause do ?

the link might be helpful to you.

link

In simple words, can you explain me what does 'connect by' achieve in your query (...... CONNECT BY level < 101) ?

dual table is automatically created in oracle with data dictionary ,sys is owner of this dual table.

Dual table is generally used for the completeness of SELECT clause syntax, cause select and from clauses are mandatory but many calculations do not need to select from actual table.
e.g. select 5*9
from dual;

Hi

Try this query

select greatest(10,15,20,30,) from dual

greatest value
-------------
30

Dual table is an hidden table in oracle.
dual table having single attribute.
we can store calculated value temporerly in the dual table.

For Example:

select sysdate from dual;

it store system date temporerly in dual table.