Home » SQL & PL/SQL » SQL & PL/SQL » Sequence Value Retrieval
Sequence Value Retrieval [message #6944] Tue, 13 May 2003 04:15 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: FOR loop
Next Topic: Missing Select Keyword
Goto Forum:
  


Current Time: Fri Apr 26 17:03:43 CDT 2024