What does a dual table in oracle do? Where will this table be use at?
What does a dual table in oracle do? Where will this table be use at?
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.
Last edited by pat.mclean; 06-01-2006 at 06:47 AM.
Very good answer.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.
Thank you very much! It really was a help in answering my inquiry. Hope you will stay there for my other questions in mind.
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
select max(12,54,2,75,142) from dual
it shows the error message invalid identifier
anybody help me....
SQL> SELECT greatest(12,54,2,75,142) from dual;
GREATEST(12,54,2,75,142)
------------------------
142
Max function takes only one parameter.
SQL> SELECT max(sal) FROM EMP;
MAX(SAL)
---------
9350
Last edited by krishnaindia2007; 10-06-2008 at 01:19 AM.
Re: Dual Table in Oracle
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;
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
Re: Dual Table in Oracle
dual table is automatically created in oracle with data dictionary ,sys is owner of this dual table.
In simple words, can you explain me what does 'connect by' achieve in your query (...... CONNECT BY level < 101) ?
Re: Dual Table in Oracle
In SQL Server, there exists no 'dual' table. Instead you can query in SQL Server like 'select 2*2' will give you the required output. {PFA the image file 1.jpg} for the output in the SQL Server.
(No Column Name)
------------------
4
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
Posting Permissions
Reply With Quote
