Re: PL/SQL procedure and input array parameter

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Dec 1999 07:54:38 -0500
Message-ID: <u7lm6skao6g4n32up05t7c3trhrb51g4jt@4ax.com>

A copy of this was sent to "Matt Houseman" <mhousema_at_ix.netcom.com> (if that email address didn't require changing) On Wed, 29 Dec 1999 18:30:47 -0800, you wrote:

>All,
>
>Is there an easy way to bind an input array into the where clause of a SQL
>statement?
>
>For example:
>
>TYPE g_foo_array IS TABLE OF foo.foo_id%TYPE INDEX BY BINARY_INTEGER;
>
>PROCEDURE foo ( foo_array IN g_foo_array ) IS
>
> CURSOR cur_find_fnd_acct_per IS
> select bar from foo where foo_id IN foo_array;
>
>BEGIN
> ...
>END;
>
>Thanks in advance,
>Matt Houseman
>
>

Not using a PLSQL table type but using a SQL (object) table type -- you can. Here is a small example:

tkyte_at_8.0> REM instead of putting a type in a spec, do this:
tkyte_at_8.0> 
tkyte_at_8.0> create or replace type myTableType as table of number;

  2 /

Type created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> REM here is an example of selecting from a local variable that is a

pl/sql

tkyte_at_8.0> REM table filled in at run time.  Do an aggregate and a sort...
tkyte_at_8.0> 
tkyte_at_8.0> declare
  2          l_x     myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );

  3
  3 begin

  4          for x in ( select sum( a.column_value ) val
  5                  from THE ( select cast( l_x as mytableType ) from dual ) a
  6                                   )
  7          loop
  8                  dbms_output.put_line( x.val );
  9          end loop;
 10  
 10          dbms_output.put_line( '---------' );
 11  
 11          for x in ( select a.column_value  val
 12                  from THE ( select cast( l_x as mytableType ) from dual ) a
 13                                   order by a.column_value desc )
 14          loop
 15                  dbms_output.put_line( x.val );
 16          end loop;

 17 end;
 18 /
45

---

9
8
7
6
5
4
3
2
1

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
tkyte_at_8.0>
tkyte_at_8.0> create or replace function getMyTableType return myTableType   2 as
  3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   4 begin
  5 return l_x;
  6 end;
  7 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> REM here we go... selecting from it:
tkyte_at_8.0> 
tkyte_at_8.0> select a.column_value  val

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

       VAL

---

         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

so, you can do some interesting things with this, like use a comma delimited list of values in an 'IN' clause, eg:

tkyte_at_8i> REM Example 2, works in 8.0 and up.  Uses an object type (new SQL
tkyte_at_8i> REM Type) to let you "select * from plsql function".  Instead
tkyte_at_8i> REM of calling function to return a result set, use a result
tkyte_at_8i> REM set to call a function
tkyte_at_8i> 
tkyte_at_8i> create or replace type myTableType as table of varchar2(20);

  2 /

Type created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> create or replace function test( p_str in varchar2 ) return

myTableType
  2 as

  3      l_str   long default p_str || ',';
  4      l_n        number;
  5      l_data    myTableType := myTabletype();
  6  begin
  7      loop
  8          l_n := instr( l_str, ',' );
  9          exit when (nvl(l_n,0) = 0);
 10          l_data.extend;
 11          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12          l_str := substr( l_str, l_n+1 );
 13      end loop;
 14      return l_data;

 15 end;
 16 /

Function created.

tkyte_at_8i> 
tkyte_at_8i> REM here we go... selecting from it:
tkyte_at_8i> 
tkyte_at_8i> select a.column_value  val

  2 from THE ( select cast( test( 'How,Now,Brown,Cow' ) as mytableType ) from dual ) a
  3 /

VAL

---

How
Now
Brown
Cow

The function test parses the list into discrete values, puts them in a table type and returns it -- you can "select * from T where C in ( ... test( 'a,b,c' ) ... )"

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 30 1999 - 06:54:38 CST