0

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

1 Answer 1

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;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.