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,

What's the type of v_areas_hijas_tmp?

kfinity
– kfinity

2018-02-07 17:41:08 +00:00
Commented Feb 7, 2018 at 17:41 — kfinity
– kfinity, Commented Feb 7, 2018 at 17:41
@kfinity same as v_areas_hijas: dbms_sql.number_table;

Alfredo M
– Alfredo M

2018-02-07 17:41:59 +00:00
Commented Feb 7, 2018 at 17:41 — Alfredo M
– Alfredo M, Commented Feb 7, 2018 at 17:41

William Robertson · Accepted Answer · 2018-02-07 18:18:56Z

0

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.

edited Feb 7, 2018 at 18:18

answered Feb 7, 2018 at 17:17

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Alfredo M Over a year ago

mmm nop same PLS-00382: expression is of wrong type

kfinity Over a year ago

What's the type of idareapadre? Is it a number?

Alfredo M Over a year ago

yes it's a number too, but: Error(45,29): PL/SQL: ORA-00932: tipos de dato inconsistentes: se esperaba UDT se ha obtenido CHAR

kfinity Over a year ago

I think the problem is that v_areas_hijas_tmp is not a TABLE OF whatever idarea is. Please show us your entire code.

kfinity Over a year ago

Okay, it looks like the UDT error is because dbms_sql.number_table is a "User Defined Type" - it's part of a package - so it doesn't work the same way as regular collections. If you create your own SQL type (or use a predefined one like dbms_debug_vc2coll) this code will work fine.

|

Collectives™ on Stack Overflow

dbms_sql.number_table in IN statement

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related