Re: Using VARRAY in "IN" clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 17 Nov 1999 07:40:05 -0500
Message-ID: <2qEyOBItl2E6jpw6Dkl37g7py+wV@4ax.com>

A copy of this was sent to "Chad Sheley" <csheley_at_usa.capgemini.com> (if that email address didn't require changing) On Tue, 16 Nov 1999 12:11:17 -0600, you wrote:

>Does anyone know how to do what I'm trying to illustrate in the following
>"make believe" SQL statement? I want to use an array as the variable in the
>"IN" clause.
>
>declare
> TYPE x_type IS VARRAY(10) OF INTEGER;
> x x_type := x_type(1,2,3,4);
>begin
> select the_name
> from name_table
> where name_id in (x);
>end;
>
>Thank you,
>
>Chad Sheley
>Senior Consultant
>Cap Gemini
>Des Moines, IA
>

You can't do it with a varray -- a nested table type yes, but not a varray. Also, the nested table type must be known to SQL (outside of plsql, your type above is known only to plsql, not to the sql engine). Here are some examples showing how to do what you want:

tkyte_at_8.0> set echo on
tkyte_at_8.0> set serveroutput on
tkyte_at_8.0> 
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> select a.column_value val
  2 from the ( select cast( myTableType(1,2) as mytableType ) from dual ) a   3 /

       VAL

---

         1
         2

tkyte_at_8.0>
tkyte_at_8.0> declare
  2 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   3 begin

  4      for x in ( select * from all_users where user_id in
  5                      ( select sum( a.column_value ) val
  6                          from THE ( select cast( l_x as mytableType ) from
dual ) a )
  7                   )
  8      loop
  9          dbms_output.put_line( x.username );
 10      end loop;

 11 end;
 12 /

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.

tkyte_at_8.0>

--
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 Wed Nov 17 1999 - 06:40:05 CST