Combine 2 VARRAYS Oracle PL/SQL

Question

I have a function that returns an Array of codes (NUMBER).

select GET_USER_OFFICE_CODES(10) FROM DUAL;
--output--
DB.OFFICE_CODES(3,4,5,6,7,8,9)

and

select GET_USER_OFFICE_CODES(2) FROM DUAL;    
--output--
DB.OFFICE_CODES(1,10,14,21)

In a function I am trying to combine 2 ARRAYS of the same type. I get an error "wrong number of types of arguments in call to 'MULTISET_UNION_ALL'"

create or replace function COMBINE_OFFICE_CODES(
  in_local_office_code NUMBER,
  in_regional_office_code NUMBER
)
RETURN OFFICE_CODES
IS
  local_office_codes OFFICE_CODES;
  regional_office_codes OFFICE_CODES;
  combined_office_codes OFFICE_CODES; 
Begin
  local_office_codes := GET_USER_OFFICE_CODES(in_local_office_code);
  regional_office_codes := GET_USER_OFFICE_CODES(in_regional_office_code);
  combined_office_codes := local_office_codes MULTISET UNION ALL regional_office_codes;

  RETURN combined_office_codes;
end

Answer 1

You can use UNION ALL;

create type OFFICES_CODES is varray(100) of number;

declare
  local_office_codes OFFICES_CODES := OFFICES_CODES(1,2);
  regional_office_codes OFFICES_CODES := OFFICES_CODES(3,4);
  combined_office_codes OFFICES_CODES; 
Begin

  SELECT * BULK COLLECT INTO  combined_office_codes FROM  
  (select *from table(local_office_codes) 
    UNION ALL  
  select *from table(regional_office_codes));
 
 FOR i in 1..combined_office_codes.count loop
    DBMS_OUTPUT.PUT_LINE(combined_office_codes(i));
 end loop;
 
end;
/

Statement processed. 1 2 3 4

*************EIDT *********

You can also get rid of local variables and use directly functions. Try this:

create or replace type OFFICES_CODES is varray(100) of number;

create or replace function  get_office_codes (nNum number) return OFFICES_CODES
is
vRet offices_codes;
begin
    if nNum = 1 then
        vRet := offices_codes(1,4);
    else
        vRet := offices_codes(3,6);
    end if;
    return vRet;
end;
/


declare
  combined_office_codes OFFICES_CODES; 
Begin

  SELECT * BULK COLLECT INTO  combined_office_codes FROM  
  (select * from table(get_office_codes(1)) 
    UNION ALL  
  select *from table(get_office_codes(2))
  ORDER BY 1);
 
 FOR i in 1..combined_office_codes.count loop
    DBMS_OUTPUT.PUT_LINE(combined_office_codes(i));
 end loop;
 
end;
/