F0RR F0RR - 2 years ago 111
SQL Question

Query several NEXTVAL from sequence in one statement

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?

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.

Answer Source

You can use this:

select your_sequence.nextval
from (
   select level 
   from dual 
   connect by level < 1000
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download