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

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 Tue Jul 22 2003 - 14:26:08 CDT