Thanks for that, it looks mighty close to what I am after.
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9vb1mt0vo4_at_drn.newsguy.com...
> In article <RVUR7.18$6r1.636_at_news0.optus.net.au>, "Matt says...
> >
> >Hi All,
> >is there some way that I can create a "table" or "view" like structure
(i.e.
> >something that can be described and queried from) that is actually the
> >results from a PLSQL program?
> >
> >Matt.
> >
> >
>
> Yes, in 8i you need to build the entire result set and then return it. In
9i,
> you can return rows as you build them.
>
> Here is an 8i example:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myScalarType as
object
> 2 ( x int,
> 3 y varchar2(15),
> 4 z date
> 5 )
> 6 /
>
> Type created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myArrayType as
table
> of myScalarType
> 2 /
>
> Type created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace function foo(p_nrows
in
> number) return myArrayType
> 2 as
> 3 l_data myArrayType := myArrayType();
> 4 begin
> 5 for i in 1 .. p_nrows
> 6 loop
> 7 l_data.extend;
> 8 l_data(l_data.count) := myScalarType( i, 'row ' || i,
> sysdate+i );
> 9 end loop;
> 10 return l_data;
> 11 end;
> 12 /
>
> Function created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from table( cast(foo(5) as
> myArrayType) );
>
> X Y Z
> ---------- --------------- ---------
> 1 row 1 14-DEC-01
> 2 row 2 15-DEC-01
> 3 row 3 16-DEC-01
> 4 row 4 17-DEC-01
> 5 row 5 18-DEC-01
>
>
>
> Now, in 9i this can be much more efficient -- we don't have to build the
array
> and then return it:
>
> ops$tkyte_at_ORA9I.WORLD> create or replace function foo2(p_nrows in number)
return
> myArrayType
> 2 pipelined
> 3 as
> 4 begin
> 5 for i in 1 .. p_nrows
> 6 loop
> 7 pipe row ( myScalarType( i, 'row ' || i,
sysdate+i ) );
> 8 end loop;
> 9 return;
> 10 end;
> 11 /
>
> Function created.
>
> ops$tkyte_at_ORA9I.WORLD>
> ops$tkyte_at_ORA9I.WORLD> select * from table( cast(foo2(5) as
myArrayType) );
>
> X Y Z
> ---------- --------------- ---------
> 1 row 1 14-DEC-01
> 2 row 2 15-DEC-01
> 3 row 3 16-DEC-01
> 4 row 4 17-DEC-01
> 5 row 5 18-DEC-01
>
>
> that'll return rows as they are produced...
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Thu Dec 13 2001 - 17:25:05 CST
