Friday, 7 December 2012

Multipe rows from dual table


Dual is a system table in oracle which has only one row and one column. In normal circumstances it should not return more than one rows. But there could be a situation where in you need an output with multiple rows.

Let's consider a simple example here. 



Example 1 : You want to print numbers from 1 to 10.

SQL> select level from dual connect by level <=10;
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10


Example 2 : You want to print square and cube of numbers from 1 to 10.

SQL> select level "No", power(level,2) "Square", power(level,3) "Cube"  from dual connect by level <= 10;

        No     Square       Cube
---------- ---------- ----------
         1          1          1
         2          4          8
         3          9         27
         4         16         64
         5         25        125
         6         36        216
         7         49        343
         8         64        512
         9         81        729
        10        100       1000


Hence you can manipulate it in whatever form you want. This is how you can return multiple rows from dual table.


1 comment:

  1. Hi,

    While using CONNECT BY, I see it always leave behind one row.
    Suppose I have a condition based on which I have to generate constant rows like
    SELECT 1 FROM DUAL WHERE ROWNUM < N;
    Here if N = 0, still it gives out single row.
    Thanks for your help in advance.

    ReplyDelete

back to top