Re: Smart Views?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 13 Dec 2001 12:05:49 -0800
Message-ID: <9vb1mt0vo4@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 - 14:05:49 CST