Re: Function returning an aray and us it in an where clause

From: Billy <vslabs_at_onwe.co.za>
Date: 27 Jun 2005 05:18:42 -0700
Message-ID: <1119874721.985998.44230@g14g2000cwa.googlegroups.com>

Björn Wächter wrote:

> While testing the code I had a new idea:
> Is it possible to return more than one column
> with such an stored function?

Yes.

SQL> create or replace type TStuff as object   2 (

  3     id      number,
  4     name    varchar2(30)

  5 );
  6 /

Type created.

SQL> create or replace type TStuffArray as table of TStuff;   2 /

Type created.

SQL> create or replace function MyStuff return TStuffArray   2 is
  3 array TStuffArray;
  4 begin
  5 array := TStuffArray( TStuff(1,'Foo1'), TStuff(2,'Foo2'), TStuff(3,'Foo3') );
  6 return( array );
  7* end;
SQL> / Function created.

SQL> select * from TABLE( MyStuff );

        ID NAME

---------- ------------------------------
         1 Foo1
         2 Foo2
         3 Foo3

SQL> However.. you should rather look at pipelined table functions too. It provides a far superior method for PL/SQL functions to return data sets, including the ability for the PL/SQL function to be multi-threaded.

Details in PL/SQL User's Guide and Reference.

> The problem: I want
> to use one of the colums like in the first
> example to select from another table/view and the
> secound one should only be displayed.

Join it. E.g.

select
  NVL(t.object_name,'*null*') as NAME1,
  s.name as NAME2
from TABLE(MyStuff) s,

     all_objects t
where s.id = t.object_id (+);

NAME1                          NAME2
------------------------------ ------------------------------
*null*                         Foo1
*null*                         Foo2
I_OBJ#                         Foo3

But be aware that as the data resides in PL/SQL, the SQL engine needs to make a context switch to the PL/SQL engine, run the code, and then build a SQL "result set" from it. This data set it not indexed.

This is not really performant when dealing with anything but small data volumes. Data should rather be stored in Oracle tables.. and temporary session/processing data can always be stored in temp tables if need be.

--
Billy

Received on Mon Jun 27 2005 - 07:18:42 CDT