Re: returning a constant set of numbers from a "select"

I understand your reasoning when using 9i, but I am on 8i which is constrained by the lack of pipe row / pipelined functionality. In 8i's case the use of RowNum predicate in where clauses would not be of equivalent use, because a STOP KEY could only be resolved after the table of integer was constructed. There is no point in building a list of say 1000 values to use rownum to limit to 50, more efficient to simply build 50. In this case, bounds on the table (via function arguments) are required for construction, and if this is the case why limit to incremental lists?

Oracle must have realised the various limitations we have touched on, when they thought of the pipe row functionality in 9i. It is also nice that in 9i you don't have to "cast" function results of collections to the type in question - it makes calls far tidier.

I do think your 9i approach improves upon Tom Kyte's though.

Kindest regards
Paul Scott

On Tue, 22 Jul 2003 12:26:08 -0700, "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote:

>No, that flexibility is elusive. Column expressions easily take care of this
>problem, making [increasing] integer list the most general solution. For
>example, you can easily do:
>
>select 50-rownum as descendingList
>from integers where rownum < 100
>
>where integers is a view defined one way or the other. In fact, we don't
>even need an explicit upper range argument to the table function in Tom's
>approach. Essentially, his code can be reduced to 10 lines
>
>
>CREATE TYPE IntSet AS TABLE OF Integer;
>/
>
>CREATE or replace FUNCTION UNSAFE
> RETURN IntSet PIPELINED IS
>BEGIN
> loop
> PIPE ROW(1);
> end loop;
>END;
>/
>
>select rownum from TABLE(UNSAFE) where rownum <= 5;
>
>
>Specifying explicit predicate in the sql query instead of some obscure table
>function argument(s) is much more intuitive.
>
>"Paul Scott" <pscott_at_r.co.uk> wrote in message
>news:j6uqhv0o4v1mve360hnu92k5ca670e9dmp_at_4ax.com...
>> I've just looked at Thomas Kyte's solution, essentially we have found
>> the same approach to solving the problem. He mentions using PIPELINED
>> which should make fetches appear faster, but I believe this is a new
>> 9i feature and I'm on 8i. In my defence, mine is a little more
>> flexible as I've allowed reverse loops and the start to be defined,
>> and it is more backward compatible. Horses for courses!
>>
>>
>>
>> On Tue, 22 Jul 2003 09:35:03 -0700, "Mikito Harakiri"
>> <mikharakiri_at_ywho.com> wrote:
>>
>> >I think pipelined version would be faster because it wouldn't necessarily
>> >materialize intermediate table. Benchmark anybody?
>> >
>> >"Paul Scott" <pscott_at_r.co.uk> wrote in message
>> >news:1g8qhvcm4sdksa27vdqgvtsgn42g1gkkdi_at_4ax.com...
>> >> I wanted this a while ago and so wrote the following. It has the
>> >> following benefits :
>> >> 1. It is fast
>> >> 2. It is flexible (caller defines range)
>> >> 3. It supports both forward and reverse loops
>> >> 4. It can be joined to other tables.
>> >>
>> >>
>> >> An example of how to use it for your situation:
>> >>
>> >> select L.COLUMN_VALUE from table(cast(Loop(1, 1000) as TY_LOOP)) L
>> >> /
>> >>
>> >> but of course you can define the range e.g 10000 downto 5 :
>> >>
>> >> select L.COLUMN_VALUE from table(cast(Loop(10000, 5) as TY_LOOP)) L
>> >> /
>> >>
>> >> --==============================================================
>> >>
>> >> create or replace type TY_LOOP as table of Integer; /* cannot be
>> >> PLS_integer in 8i! */
>> >> /
>> >>
>> >> create or replace function Loop(pStartValue PLS_Integer, pStopValue
>> >> PLS_Integer) return TY_LOOP is
>> >> /* Returns a collection of integers between pStartValue and
>> >> pStopValue,
>> >> To use in SQL :
>> >> (forward) 1. select L.COLUMN_VALUE from table(cast(Loop(1, 3) as
>> >> TY_LOOP)) L
>> >> (reverse) 2. select L.COLUMN_VALUE from table(cast(Loop(5, 3) as
>> >> TY_LOOP)) L
>> >> 1. COLUMN_VALUE 2. COLUMN_VALUE
>> >> --------------- ------------
>> >> 1 5
>> >> 2 4
>> >> 3 3
>> >>
>> >> NOTE : Null parameters behave like standard for loops : VALUE_ERROR
>> >> exception */
>> >>
>> >> vResult TY_LOOP := TY_LOOP();
>> >> begin
>> >> if pStartValue > pStopValue then
>> >> for n in reverse pStopValue..pStartValue
>> >> loop
>> >> /* for performance reason next 2 lines are NOT made into local
>> >> sub proc */
>> >> vResult.Extend;
>> >> vResult(vResult.Last) := n;
>> >> end loop;
>> >> else
>> >> for n in pStartValue..pStopValue
>> >> loop
>> >> vResult.Extend;
>> >> vResult(vResult.Last) := n;
>> >> end loop;
>> >> end if;
>> >> return vResult;
>> >> end;
>> >> /
>> >>
>> >> Paul Scott
>> >>
>> >>
>> >>
>> >>
>> >> On Mon, 21 Jul 2003 13:10:40 GMT, p!@unix.ndo.co.uk (p!) wrote:
>> >>
>> >> >hi,
>> >> >
>> >> >could anyone tell me how i can do the following:
>> >> >
>> >> >would like a select statement which returns all the numbers between a
>> >> >variable start and end point as seperate rows without using a "proper"
>> >> >table, ie:
>> >> >
>> >> >result
>> >> >----------
>> >> >1
>> >> >2
>> >> >3
>> >> >4
>> >> >5
>> >> >6
>> >> >7
>> >> >8
>> >> >9
>> >> >10
>> >> >..
>> >> >1000
>> >> >
>> >> >
>> >> >any ideas?!
>> >> >
>> >> >thanks,
>> >> >p!
>> >>
>> >
>>
>
Received on Wed Jul 23 2003 - 04:57:04 CDT