This tutorial demonstrates how to generate a series of integers using a novel application of the CONNECT BY clause first posted by Mikito Harakiri at Ask Tom "how to display selective record twice in the query?". Other techniques are discussed in the tutorials listed in the menu to the left.
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.
The effect also proves useful in situations where more than one integer series is required from a single query. See Multiple Integer Series: CONNECT BY LEVEL Method for more details and an example.
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
Laurent Schneider argues in his blog post
Bible of Oracle
that a clause like
CONNECT BY LEVEL <= 10
is an illegal construct since it has no expressions qualified with the PRIOR
operator, as dictated by this statement in the SQL Reference Manual
"in a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row." -- Oracle® Database SQL Reference 10g Release 2 (10.2)
Others argue that this statement is a documentation bug. The fact the CONNECT BY clause works without error in Oracle 10g and some 9i versions somewhat supports this view.
Note that the following queries, seemingly equivalent to the
CONNECT BY LEVEL <= 10
solution, do not produce the desired 10 rows of output (as tested in Oracle 10g).
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.
Issues
Issues with this technique, or variations of it, have been reported in Oracle versions earlier than 10.2. I have not tested these myself but here are some posts that describe problems.
- Ask Tom "Can there be an infinite DUAL?" - Weird results
- Ask Tom "Can there be an infinite DUAL?" - Weird Results (bug?)
- Ask Tom "how to display selective record twice in the query?"- minor simplification
- CONNECT BY Generator Rules | Ask Mr. Ed
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.
Acknowledgements
Mikito Harakiri, Tom Kyte, Laurent Schneider and other posters at these threads.