is it possible to generate bulk seuence numbers from a single sql query. Lets say there is a sequence name "test_seq". The curent value on that sequence is lets say 10, if i want to get the next 20 sequence in a sql query, how i will do that, i want to get those 20 values in the resultset through java.

Thanks

Hi,

documentation does not suggest there is a way to retrieve a couple of different NEXTVALs for YOURSEQENCE in a single query. But there are other options:

a) create a sequence with an INCREMENT BY 20 clause, and let the application do the filling of the gaps or
b) retrieve 20 NEXTVALs through the application or
c) create a table function making use of procedure language, retrieve 20 NEXTVALs in a loop

Would be interesting to know what you are going to settle with.

Johann

d) Use a recursive SQL to retrieve 20 values

Cheers
Sathyaram

Here's a hack...

with nextvals(counter) as
(
values (1)
union all
select counter + 1
from nextvals
where counter < 20
)
select nextval for your_sequence
from nextvals
;

Damian