Sequence Value Retrieval [message #6944] |
Tue, 13 May 2003 04:15 |
Tanveer
Messages: 7 Registered: April 2002
|
Junior Member |
|
|
How do I select "n" number of values from a sequence in a single query? For example, I want the next 10000 values from a sequence called pk_seq. I can do this by executing "select pk_seq.nextval from dual" in a for loop. Is there any way I can do this with a single select query? For any value of "n"?
|
|
|
Re: Sequence Value Retrieval [message #6955 is a reply to message #6944] |
Tue, 13 May 2003 11:49 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
With straight SQL, you can:
select pk_seq.nextval
from all_objects
where rownum <= :num_of_values;
You can use any table that has at least as many rows as values you want to retrieve.
In a PL/SQL context, you can apply the same approach and use a bulk collect:
declare
type na is table of number index by binary_integer;
v_na na;
begin
select foo.nextval bulk collect
into v_na
from all_objects
where rownum <= 5;
for i in 1..v_na.count loop
dbms_output.put_line( v_na(i) );
end loop;
end;
|
|
|
Re: Sequence Value Retrieval [message #6960 is a reply to message #6955] |
Tue, 13 May 2003 21:35 |
Tanveer
Messages: 7 Registered: April 2002
|
Junior Member |
|
|
Hi Todd,
Thanks for the reply. I already knew about this approach and I was looking for some other sort of an answer (maybe some advanced feature of Oracle that I didn't know, being a novice). Well, this won't work in my case for I can't always guarantee that I'll a table in my schema with the required no of rows. But thanks anyways.
|
|
|
Re: Sequence Value Retrieval [message #6982 is a reply to message #6960] |
Wed, 14 May 2003 12:33 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If you were looking for some other "magic" answer, why didn't you mention that in the first place?
And ALL_OBJECTS is not a table in your or any other schema, it is a data dictionary view. What is so hard about creating a skinny table (1 column) with more rows than you would ever need? Kindergarten stuff - come on.
|
|
|