This is a package, what give back table type.
But it's not working.
create or replace package account_group is
TYPE string_array IS TABLE OF varchar2;
function get_acc_by_vat_number(in_string varchar2) return string_array;
end account_group;
create or replace package body account_group is
function get_acc_by_vat_number(in_string varchar2) return string_array is
acc_array string_array;
i number;
cursor c1 is
(select to_char(d.account_number) account_number from wiz_customer_descrip d, wiz_customer_hp_life l
where commercial_flag='Y'
and l.franchise_code!='F29'
and d.account_number=l.account_number
and id_3=in_string --vat_number
--and ssn='08-09-002272' --company_reg_num
UNION
select to_char(d.account_number) account_number from wiz_customer_descrip d, wiz_customer_hp_life l
where commercial_flag='Y'
and l.franchise_code='F29'
and d.account_number=l.account_number
and id_3=in_string --vat_number
--and ssn='08-09-002272' --company_reg_num
and exists
(select 1 from WIZ_CUSTOMER_HP_OCCURRENCE p
WHERE product_category_code in ('MID','MVO')
and p.account_number=l.account_number
and p.service_address_id=l.service_address_id));
begin
i:=0;
for cur in c1 loop
i:=i+1;
acc_array(i):=cur.account_number;
end loop;
return acc_array;
end;
end account_group;