Learn Oracle with Yasser

Integer Series Generators

CONNECT BY LEVEL Method

With this technique you can generate a series of integers starting at "1" using a query like this.

select  level
from    dual
connect by level <= 10 ;
 

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

Queries Without PRIOR

The query above is a special case of a more general type of query, those that do not use the PRIOR operator. Applying the technique to a table with two rows, "a" and "b", yields some insight into how such queries work.

break on level duplicates skip 1

column path format a10

select     level, sys_connect_by_path( key, '/' ) as path, key
from       t4
connect by level <= 3
order by   level, path ;
 

     LEVEL PATH       KEY
---------- ---------- ---
         1 /a         a
         1 /b         b

         2 /a/a       a
         2 /a/b       b
         2 /b/a       a
         2 /b/b       b

         3 /a/a/a     a
         3 /a/a/b     b
         3 /a/b/a     a
         3 /a/b/b     b
         3 /b/a/a     a
         3 /b/a/b     b
         3 /b/b/a     a
         3 /b/b/b     b
 

Without a CONNECT BY condition that uses PRIOR it appears Oracle returns all possible hierarchy permutations. This effect may be useful where an exponentially increasing number of output rows is required.
Note there is some debate about whether queries without PRIOR in the CONNECT BY clause are legal or not. This is discussed further in the "Gotchas" section below.

Variables

The original syntax for this technique works fine when the number of rows is hardcoded to a value greater than or equal to 1. If the number of rows is set with a bind variable whose value can be 0, negative, or null however, the technique may not work as expected. It always generates at least one row in these cases.

clear breaks

variable v_total_rows number

execute :v_total_rows := 0

select  level
from    dual
connect by level <= :v_total_rows ;
 

     LEVEL
----------
         1
 

execute :v_total_rows := -5

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1

1 row selected.


execute :v_total_rows := null

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1

1 row selected.

 

A simple WHERE clause fixes this behaviour.

execute :v_total_rows := 0

PL/SQL procedure successfully completed.


select  level
from    dual
WHERE   :V_TOTAL_ROWS >= 1
connect by level <= :v_total_rows ;

no rows selected

 

execute :v_total_rows := -5

PL/SQL procedure successfully completed.

/

no rows selected


execute :v_total_rows := null

PL/SQL procedure successfully completed.

/

no rows selected


execute :v_total_rows := 3

PL/SQL procedure successfully completed.

/

     LEVEL
----------
         1
         2
         3

3 rows selected.

 

Day of the Week Case Study

In the next snippet we apply the technique to the day of the week scenario we examined in prior tutorials.

select
  day_of_week ,
  t.val
from
  ( select level - 1 as day_of_week
    from dual
    connect by level <= 7
  ) i
    left outer join t using( day_of_week )
order by
  day_of_week
;
 

DAY_OF_WEEK        VAL
----------- ----------
          0
          1        100
          2
          3        300
          4        400
          5        500
          6
 

Gotchas

To Use PRIOR or Not to Use PRIOR, That is the Question

select  level
from    dual
connect by
  level <= 10
  AND PRIOR DUMMY = DUMMY
;
ERROR:
ORA-01436: CONNECT BY loop in user data


 

select  level
from    dual
connect by
  level <= 10
  AND PRIOR 1 = 1
;
ERROR:
ORA-01436: CONNECT BY loop in user data


 

The following variation may be more legal than the original solution since it includes a PRIOR condition and does not produce a CONNECT BY loop, but the PL/SQL call it contains makes it perform worse (from Re: Creating N Copies of a Row using "CONNECT BY CONNECT_BY_ROOT" - Volder).

select level
from   dual
connect by
  level <= 10 and
  PRIOR DBMS_RANDOM.VALUE IS NOT NULL
;
 

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

The jury is still out on using CONNECT BY LEVEL to generate integers. Until there is a definitive answer, be aware there is a risk the technique may not work in future versions.
In Oracle 9i, if you try the CONNECT BY LEVEL technique and get a single row when expecting muliple rows, like this

select level from dual connect by level < 10 ;
 

     LEVEL
----------
         1
 

putting the query in an inline view, as in this snippet, may help (I have not tested this).

select *
from   (select level from dual connect by level < 10) ;
 

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.