dbms_sql.number_table in IN statement

Question

I have declared a number table like:

v_areas_hijas   dbms_sql.number_table;

I fill the table and then I'm trying this comparation:

  select 
  idarea
from areas 
where 
  IDAREAPADRE in v_areas_hijas;

but I'm getting:

Error(45,22): PLS-00382: expression is of wrong type

How should I do that IN statement?

EDIT 1: using "member of"

  select 
      idarea
    from areas 
    where 
      IDAREAPADRE member of v_areas_hijas;

I've got this two errors:

Error(45,29): PLS-00382:expression is of wrong type

Error(45,29): PL/SQL: ORA-00932: inconsistent datatypes expected UDT got CHAR

in the table AREAS, IDAREA and IDAREAPADRE are NUMBER,

William Robertson William Robertson · Accepted Answer · 2018-02-07T17:17:39

As in your other question, v_areas_hijas is not a table. If it's a collection type defined in SQL you can use the member of collection operator:

select idarea bulk collect into v_areas_hijas_tmp 
from   areas 
where  idareapadre member of v_areas_hijas;

For example, using the predefined type sys.dbms_debug_vc2coll (look inall_coll_types for more examples),

declare 
    t_demo sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('X','Y','Z');
    l_result integer;
begin
    select count(*) into l_result
    from   dual
    where  dummy member of t_demo;

    -- Or this:
    select count(*) into l_result
    from   dual
    where  dummy in
           ( select column_value from table(t_demo) );

    -- Or this:
    select count(*) into l_result
    from   dual d
           join table(t_demo) t on t.column_value = d.dummy;

    dbms_output.put_line(l_result);
end;

You can use the table() operator on nested table and varray types declared in packages:

create or replace package demo
as
    type demo_collection_type is table of varchar2(1);
end demo;

then

declare 
    t_demo demo.demo_collection_type := demo.demo_collection_type('X','Y','Z');
    l_result integer;
begin
    select count(*) into l_result
    from   dual
    where  dummy in
           ( select column_value from table(t_demo) t );

    dbms_output.put_line(l_result);

    -- Or:
    select count(*) into l_result
    from   dual d
           join table(t_demo) t on t.column_value = d.dummy;

    dbms_output.put_line(l_result);
end;

As of 12.1 you can't use member of for collection types declared in PL/SQL packages.

Some of these restrictions are lifted in 12.2.

dbms_sql.number_table in IN statement

1 Answers