Receiving error "Local collection types not allowed" while working with associative arrays

Question

I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix), separate the items (0,1,2,3,4) and store them in an array of numbers. Below you can see what I've done so far :

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)
    into t_dep2cust_rel
    from dual
  CONNECT BY REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL) IS NOT NULL;

end;

and I'm receiving this error message :

"Local collection types not allowed in SQL statements"

I was wondering if you could help me find the problem and tell me whether there is a better way for doing this.

Thanks in advance

Answer 1

SELECT .. INTO works only with queries that return a single row, and it can collect only scalar types. Your query returns multiple rows, and BULK COLLECT INTO should be used:

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)
    bulk collect into t_dep2cust_rel
    from dual
  CONNECT BY REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL) IS NOT NULL;
end;
/