Category Archives: sql

Select a bunch of values from the DB sequence at a time

Sometimes you need to select multiple values from certain sequence. Surprisingly it is not that easy to find the relevant information on how to do it in Google.
Here is the quick snippets for those who need it. In this snippet my_seq assumed to be a name of the previously created sequence (e.g. CREATE SEQUENCE my_seq) and 42 assumed to be the number of sequence values you want to pick from sequence in one turn.

For Oracle:

SELECT my_seq.nextval FROM dual CONNECT BY level <= 42

For H2 (I heavily use it for unit testing in favor to Oracle because it is fast, small and cool):

SELECT my_seq.nextval FROM system_range(1, 42)

It is worth to mention that in H2 you can very easily select a sequence of natural numbers by accessing system_range function. E.g. if you want to get a set of {1, 2, 3, 4, 5, 6, 7, 8} you may just do SELECT * FROM system_range(1, 8). I never knew this function before and I'm happy for being accidentally find it.