Automatically Generating Lists of Numbers in Oracle with Connect by
Automatically Generating Lists of Numbers in Oracle with Connect By
You want to generate a list of numbers for a one-time calculation or SQL query. For instance, you need a
table or view with the numbers 1 to 1000 for ranking and position calculations, but you don’t want to
create a sequence and call it 1000 times to populate your table.
Oracle provides several techniques for spontaneously generating sets of numbers. Our recipe will use
Oracle’s traditional hierarchy support via the CONNECT BY clause and the ROWNUM pseudocolumn, to build
a ready-made list of numbers.
The next SQL statement uses CONNECT BY LEVEL against the dual table, to build a list of the numbers
1 to 1000.
select rownum as my_numbers
connect by level <= 1000;
The results are hopefully obvious, but just in case you’re wondering, we return the numbers 1 to
1000 (with abridged results shown here to save space).
1000 rows selected.