Page 1 of 2 12 LastLast
Results 1 to 20 of 21

Thread: Dual Table in Oracle

  1. #1
    Contributing Member
    Join Date
    May 2006
    Answers
    64

    Dual Table in Oracle

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


  2. #2
    Junior Member
    Join Date
    Apr 2006
    Answers
    24

    Re: Dual Table in Oracle

    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.

  3. #3
    Administrator
    Join Date
    May 2006
    Answers
    331

    Re: Dual Table in Oracle

    Quote 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.


  4. #4
    Contributing Member
    Join Date
    May 2006
    Answers
    64

    Re: Dual Table in Oracle

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


  5. #5
    Contributing Member
    Join Date
    May 2006
    Answers
    85

    Re: Dual Table in Oracle

    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


  6. #6
    Expert Member
    Join Date
    May 2006
    Answers
    114

    Re: Dual Table in Oracle

    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


  7. #7
    Junior Member
    Join Date
    Jul 2006
    Answers
    1

    Re: Dual Table in Oracle

    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


  8. #8
    Junior Member
    Join Date
    Oct 2008
    Answers
    1

    Re: Dual Table in Oracle

    Quote Originally Posted by janelyn View Post
    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....


  9. #9
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Dual Table in Oracle

    Quote Originally Posted by a_kumarb View Post
    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.

  10. #10

    Thumbs up 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;


  11. #11
    Junior Member
    Join Date
    Sep 2008
    Answers
    2

    Re: Dual Table in Oracle

    Quote Originally Posted by janelyn View Post
    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


  12. #12
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Dual Table in Oracle

    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.


  13. #13
    Junior Member
    Join Date
    Nov 2008
    Answers
    1

    Re: Dual Table in Oracle

    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


  14. #14
    Junior Member
    Join Date
    Jan 2008
    Answers
    3

    Re: Dual Table in Oracle

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

    what does 'CONNECT BY' clause do ?


  15. #15
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Dual Table in Oracle

    the link might be helpful to you.

    link


  16. #16
    Junior Member
    Join Date
    Nov 2008
    Answers
    2

    Arrow Re: Dual Table in Oracle

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


  17. #17
    Junior Member
    Join Date
    Jan 2008
    Answers
    3

    Re: Dual Table in Oracle

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


  18. #18
    Junior Member
    Join Date
    Jan 2008
    Answers
    3

    Smile Re: Dual Table in Oracle

    Quote Originally Posted by sunshine60india View Post
    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;

    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

    Attached Images Attached Images Dual Table in Oracle-1-jpg 

  19. #19
    Junior Member
    Join Date
    Mar 2009
    Answers
    7

    Re: Dual Table in Oracle

    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;


  20. #20
    Junior Member
    Join Date
    Oct 2008
    Answers
    28

    Re: Dual Table in Oracle

    Hi

    Try this query

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

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


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact