Re: Getting NON-existing data

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 25 Jun 1999 14:25:28 +0100
Message-ID: <930317427.18993.0.nnrp-01.9e984b29@news.demon.co.uk>

An Oracle 8 solution: use CAST of a pl/sql array -

create or replace type int_arr as table of integer; /

create or replace function get_ints (

     how_many in number
) return int_arr
as

     v_int_arr int_arr := int_arr();
begin

     for r1 in 1..how_many loop
          v_int_arr.extend;
          v_int_arr(r1) := r1;
     end loop;

     return v_int_arr;

end;
/

rem
rem How to get a list of 10 numbers that appear to be rem selected from a table
rem

select

     *
from

     the(select cast(get_ints(10) as int_arr) from dual) ;

rem
rem Now a demostration that you can do this, and join rem the result to another table - this also happens to rem answer another question posed on the newsgroup rem a couple of days ago.
rem

rem build a demo table

create table demo(

     v1 varchar2(5),
     n1 number

);

insert into demo values('XX',4);
insert into demo values('YY',3);

rem
rem Join demo to the cast list
rem

select

     v1,
     n1,
     column_value
from
     demo,
     the(select cast(get_ints(10) as int_arr) from dual) nlist
where
     nlist.column_value <= n1
order by
     v1,
     column_value

;

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Niek Kerkemeijer wrote in message <376F4EEE.1383_at_nob.nl>...
>Hi,
>
>I have a nice question:
>I need oracle to generate a number-range for me without having
>a table containing these values.
>
>Example:
>I have an empty database (there are no tables in it) and I need
>a query which results in the following:
>
>+------+
>| id |
>+------+
>| 1 |
>| 2 |
>| 3 |
>| 4 |
>| 5 |
>| 6 |
>| 7 |
>| 8 |
>| 9 |
>| 10 |
>+------+
>
>
>Any ideas?
>
>
>Niek.
Received on Fri Jun 25 1999 - 08:25:28 CDT