Receiving error "can not access rows from a non-nested table item" while working with associative arrays

Question

Below , I'm trying to use the data of the array I've declared, in the where clause of the query but I receive the following error :

declare

  v_input varchar2(400) := '0,1,2,3,4';

  type t_dep2custrel_type is table of number;
  t_dep2cust_rel t_dep2custrel_type;

begin


  select regexp_substr(v_input, '[^,]+', 1, level) zz
    bulk collect
    into t_dep2cust_rel
    from dual
  connect by regexp_substr(v_input, '[^,]+', 1, level) is not null;


  insert into bb_tmp_natonalcode_accnumber
    (identificationnumber)
    select b.customer_num
      from [sample_table] b
     where b.dpst2cust_rel_cod in (select * from table(t_dep2cust_rel)); /* here I'm trying to use the array*/

  commit;

end;

1. "Local collection types not allowed in sql statement "
2. "can not access rows from a non-nested table item" (ORA-22905)

I was wondering if you could help me with this.

Thanks in advance

Answer 1

You must create the type as database object:

CREATE OR REPLACE type t_dep2custrel_type as table of number;

Or use MEMBER OF:

select b.customer_num
  from [sample_table] b
 where b.dpst2cust_rel_cod MEMBER OF t_dep2cust_rel; 

should also work.