SQL Snippets ™: Tutorials for Oracle Developers

With the CONNECT BY LEVEL approach there is also no need to use the Join Method. Multiple integer series can be created using a query like this one (the PATH column is included to illustrate how the query works)

set null "(null)"

break on key duplicates skip 1

column path format a10

select
  key, qty, level as integer_value, sys_connect_by_path( key, '/' ) as path
from
  t3
where
  qty >= 1
connect by
  KEY = PRIOR KEY and
  prior dbms_random.value is not null and
  level <= t3.qty
order by
  key, integer_value
;
 

KEY        QTY INTEGER_VALUE PATH
--- ---------- ------------- ----------
c            1             1 /c

d            2             1 /d
d            2             2 /d/d

e            3             1 /e
e            3             2 /e/e
e            3             3 /e/e/e
 

or this approach (only possible on version 10g or greater)

select
  key, qty, level as integer_value, sys_connect_by_path( key, '/' ) as path
from
  t3
where
  qty >= 1
connect by
  KEY = CONNECT_BY_ROOT KEY and
  level <= t3.qty
order by
  key, integer_value
;
 

KEY        QTY INTEGER_VALUE PATH
--- ---------- ------------- ----------
c            1             1 /c

d            2             1 /d
d            2             2 /d/d

e            3             1 /e
e            3             2 /e/e
e            3             3 /e/e/e
 

Note these approaches will not work for rows where no integer series is required, like the rows with KEY in ( 'a', 'b' ).

Gotchas

The CONNECT_BY_ROOT technique may not work without error in all cases and it may not work in Oracle versions beyond 10g. This is because it violates two restrictions documented at Hierarchical Query Operators:

1. "In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator."
2. "You cannot specify (CONNECT_BY_ROOT) in the START WITH condition or the CONNECT BY condition."

The fact that the query above contradicts the documentation yet works without error in 10g suggests a bug in either the documentation or the SQL engine.

On my system the following variation of the CONNECT_BY_ROOT query raised some rather severe ORA errors casting further doubt on the technique's reliability (do not run this query on your own systems).

select
  key, qty, level as integer_value
from
  t3
start with
  qty >= 1
connect by
  KEY = CONNECT_BY_ROOT KEY and
  level <= t3.qty
order by
  key, integer_value
;
ERROR at line 1:
ORA-03113: end-of-file on communication channel

ERROR:
ORA-03114: not connected to ORACLE

From the .trc file:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]
[__VInfreq__msqopnws+2740] [PC:0x30E2580] [ADDR:0x2C] [UNABLE_TO_READ] []