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

From: Billy <vslabs_at_onwe.co.za>
Date: 27 Jun 2005 00:32:37 -0700
Message-ID: <1119857557.804194.184410@g47g2000cwa.googlegroups.com>

Björn Wächter wrote:
>
> I want to write an stored function
> which returns an array of numbers.
> Afterwards I want to use this function
> in an where clause like:
>
> SELECT *
> FROM
> VI_USER
> WHERE
> USER_ID IN GetManagers(2)

Yes it is. Just make sure that it is performant and scalable and do not use this feature and code yourself into a corner. Data should live in Oracle (i.e. tables and temp tables) - not in PL/SQL. Various reasons such as referential integrity, indexing, scalability, concurrency and so on.

SQL> create or replace type TList as table of varchar2(4000);   2 /

Type created.

SQL> create or replace function MyList return TList is   2 l TList;
  3 begin

  4     l := TList( 'Jack', 'John', 'Chris' );
  5     return( l );

  6 end;
  7 /

Function created.

SQL> select * from TABLE( MyList );

COLUMN_VALUE

---

Jack
John
Chris

SQL> select created from all_users where username in (select UPPER(column_value) from TABLE( MyList ) );

CREATED

---

29-SEP-03

--
Billy

Received on Mon Jun 27 2005 - 02:32:37 CDT