Re: select ... where col in (collection)

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 08 Sep 2003 21:17:19 +0100
Message-ID: <kdoplvgrm5jec3hqvco1peaf4jcjd7ee0j@4ax.com>

On Sun, 07 Sep 2003 21:30:35 GMT, roger <rsr_at_rogerware.com> wrote:

>I wonder if I'm missing something obvious, or if I'm trying to do
>the impossible...
>
>What I want is, within PL/SQL, to use a collection with the IN operator
>in an SQL statement.
>
>Something like so...
>
> create or replace type numtab is table of number;
> /
>
> create or replace procedure cproc (cvar numtab)
> is
> begin
> for c in (select * from all_users where user_id in (cvar) )
> loop
> -- one row from the set of id values in cvar...
> end loop;
> end;
> /
>
>But compiling cproc gives
> PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got RSR.NUMTAB
>
>
>Should I be able to do this kind of thing or not?
>If so, what is the magic syntax I'm missing?
>
>Thanks for your help and patience.

 The TABLE() operator, although you have to jump through a few syntactic hoops.

SQL> create or replace type numtab is table of number   2 /

Type created.

SQL> create or replace procedure cproc (cvar numtab)   2 is
  3 begin

  4      for c in (select *
  5                from   all_users
  6                where  user_id in (SELECT * from TABLE(CAST(cvar AS
numtab))))
  7      loop
  8          dbms_output.put_line(c.user_id);
  9      end loop;

 10 end;
 11 /

Procedure created.

[ If you don't add the CAST(cvat AS numtab) it complains that you can't select from a non-nested table type; don't know why the CAST is required since it's already clear it's a table type from the parameter. ]

SQL> exec dbms_output.enable(1000000);

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2 cvar numtab := numtab(0, 5, 11, 19);   3 begin
  4 cproc(cvar);
  5 end;
  6 /
0
5
11
19

PL/SQL procedure successfully completed.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Mon Sep 08 2003 - 15:17:19 CDT