Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Selecting from sequence a specific number
Dears,
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.
And
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
i.e.:
Next value
Next value + 1
Next value + 2
... ... ...
My need is:
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!!!!!!!!!!
Thanks Received on Thu Nov 25 2004 - 07:46:08 CST