Query several NEXTVAL from sequence in one statement

Question

Basically, I need to query about a thousand NEXTVAL from a sequence. I can query them in a loop, or I can query them through a join with a reeeeeally big table.

Is there any less hacky way?

Upd. Basically, I have a schedule of operations on objects. Each object has either a generated UUID, or an ID from database. After I calculate an optimal schedule, I need to write it into DB, but every ID in the table HAS to be from a sequence. So I need to query some IDs from that sequence. The problem is that looping query is slow, since the DB is really far from me, and I can't just loose several seconds while executing dozens of queries in a loop. So I need to query all those new IDs in one query.

Also NB that you may need/want to use a large fetchsize to avoid still having an RTT per row returned: http://stackoverflow.com/a/17331855/32453 — rogerdpack, Jun 26 '13 at 22:59

score 42 · Accepted Answer · answered Nov 28 '11 at 08:10

42

You can use this:

select your_sequence.nextval
from (
   select level 
   from dual 
   connect by level < 1000
);

answered Nov 28 '11 at 08:10

also see a slightly modified version of this in Florin Ghita's response, as well. – rogerdpack Jun 25 '13 at 22:21
Am I correct in thinking that the results may or may not be contiguous? – EvilTeach Nov 07 '18 at 16:24

score 27 · Answer 2 · edited May 23 '17 at 12:01

Depends on what you want to do with them.

If you insert them in a table, you can unse seq.nexval in the insert query. (As explained here: How can I insert multiple rows into oracle with a sequence value?)

If you use them in a loop, you can fetch them in that loop.

What do you want to do with them?

As I know, you can't fetch multiple values from sequence.

UPDATE: a_horse_with_no_name's aswer can be improved like this:

select your_sequence.nextval
from dual 
connect by level < 1000

:)

score 3 · Answer 3 · answered Jun 25 '18 at 14:24

My (H2) unit test database did not support "connect by", so I had to come up with something else:

    with temp_id_table (lvl) as 
       (select 1 as lvl from dual 
        union all 
        select lvl+1 
          from temp_id_table 
          where lvl < 1000) 
    select sequence_name.nextval 
    from temp_id_table;

score -4 · Answer 4 · edited Sep 30 '14 at 20:48

-4

select sequence_name.nextval
from dual
connect by level < number of values you want to print;

edited Sep 30 '14 at 20:48

Rob Bajorek

6,382
7
44
51

answered Sep 30 '14 at 20:25

user4096547

1

3

This is identical to other answers. – Jon Heller Sep 30 '14 at 21:00
This will give you **one less** than "number of values you want to print" – James Bell Apr 12 '17 at 19:45

Query several NEXTVAL from sequence in one statement

4 Answers4

Linked