Unanswered: Selecting from sequence a specific number
I'm having small requirment with a sequence.
Lets assume that I have sequence named "yyy_seq"
If I executed "select yyy_seq.nextval from dual"
==> it will get me the value of next item in the sequence and advance the pointer of the sequence for the value after it.
if I executed "select yyy_seq.nextval from table_zzz"
==> it will get me result set of range of sequence starting from next value and ending with a value equals the start + # of rows in the table_zzz
Next value + 1
Next value + 2
Next value + # of rows in table_zzz
My need is:
I'm having a sequence, and I want to select range of values starting with the next value of this sequence and with length x, this specific value (x) is not known untill run time, and dynamic.
I have other solutions but there are problems in each one:
1- Loop x times over "select yyy_seq.nextval from dual" ==> From performance wise is not acceptable, I may have 1000 items so it will be very slow.
2- Get the first item in the sequence and then alter the sequence to start with this value + x, as I don't have this privilege on DB.
What I think should do the trick is to have the select statment return any dummy rows so generate the sequence as I wish, but it seems I can't figure it out!!!!!!!!!!
As I understand it, what is wrong with.. select yyy_seq.nextval some_big_table_you_know_has_as_many_as_your_sequen ce_number where rownum <= how_many_sequences .. if they are just 2K or less, you can use the all_objects table in this case.
Also, If I understand correctly what you are trying to do, it seems that your approach will fail on a multi-user environment in the case that this sequence is part of a primary key number. What happens when two users start the same process at relative times ?
Since you require a block of numbers at a time, a sequence may not be the best way to get them.
The simplest way is to have a table that stores one row of data with one column.
Have a small function that does the following:
When a request for numbers comes in, use a "select for update" cursor to get the current row, increment it by the desired total numbers required, then update this table. After all this is done, send the number range back to the program that needs it.
This way, you should not have any problems with two different users requiring a block of numbers at the same time.