Ref cursor populated in explicit loop

[nagornyi]

I wonder if it is possible to populate ref cursor in explicit loop. For example, let's say I need a recordset of sequential numbers
1
2
3
...
1000000
but do not have any table to select them from. These numbers can easily be generated in a loop by incrementing an integer variable. But looks like to populate ref cursor we still need to SELECT from someting. Can we use loop or yet more involed logic to generate a ref cordset?

 

[saketbansal]

u can always select from DUAL....

but this will return one row only...

HTH...
Saket

 

[nagornyi]

Good to start...but too far from what's needed.

 

[sem]

A number of workarounds exist:
1. you may query rownum from some known large table or view (all_objects is a reasonable candidate)
2. You may create some collection type (namely table of numbers), populate it and then return REF CURSOR selecting from it (after casting it to table).

Regards, Dima

http://seminihin.narod.ru

 

[nagornyi]

Thank you Dima.
1. Unfortunately, there are no such big tables in the DB. When putting "1000000" as the last number of sequence, I just was thinking if I should put another zero or two to make it clear that rownum is not an option here.
2. Creating table, populating it and then selecting from it is fine, this option is available for any DB. Somewhy I had and idea that ref cursor is more poverful tool that could do the job in other ways.

 

[sem]

Ref cursor as you may expect stands for Reference to Cursor; cursor obviously assumes some query. Unfortunately I can't find any reason in query returning numbers from 1 to 1000000, this task is probably from another area
BTW, the second solution dealt with variables of some collection type, not real tables. The power of REF CURSOR is that you pass a handler rather then value, thus you may feel the full strength of client/server, when client obtains only those rows he really needs, no the whole bunch at once.
BTW even from table with 2 rows you may get Cartesian product of any size.

Regards, Dima

http://seminihin.narod.ru