Hi, I am trying to pass a array of records to a function as in parameter, which in turn calls another function that returns an array of varchar.
I have created a package to have relevant types and functions. Please find the below package, and an anonymous block calling its components.
Please help me solve the error encountered while passing the array as IN parameter.
======================
PACKAGE SPECIFICATION
======================
create or replace package address_array
as
type address2_id_array is table of varchar2 (255);
v_address2_id_array address2_id_array;
type addr_record is record (
r_row_number varchar2 (255),
r_street_number varchar2 (255),
r_dirprefix varchar2 (255),
r_street_name varchar2 (255),
r_street_type varchar2 (255),
r_dirsuffix varchar2 (255),
r_suffix varchar2 (255),
r_city varchar2 (255),
r_province varchar2 (255)
);
v_addr_record addr_record;
type addr_record_array is table of addr_record;
v_addr_record_array addr_record_array;
type id_row is record (
v_row_number varchar2 (255),
v_id_array address2_id_array
);
type id_row_array is table of id_row;
v_id_row_array id_row_array;
function get_address_ids
(
i_addr_record IN addr_record
)
RETURN address2_id_array;
function send_addr_record
(
arr_addr IN addr_record_array
)
RETURN id_row_array;
end address_array;
--Package ADDRESS_ARRAY compiled.
==============
PACKAGE BODY
==============
create or replace package body address_array
as
function get_address_ids
(
i_addr_record IN addr_record
)
RETURN address2_id_array
as
v_id_array address2_id_array;
begin
SELECT id
BULK COLLECT INTO v_id_array
FROM address2
WHERE nvl(street_number,'null') = nvl(i_addr_record.r_street_number,'null')
AND nvl(dirprefix,'null') = nvl(i_addr_record.r_dirprefix,'null')
AND nvl(street_name,'null') = nvl(i_addr_record.r_street_name,'null')
AND nvl(street_type,'null') = nvl(i_addr_record.r_street_type,'null')
AND nvl(dirsuffix,'null') = nvl(i_addr_record.r_dirsuffix,'null')
AND nvl(suffix,'null') = nvl(i_addr_record.r_suffix,'null')
AND nvl(city,'null') = nvl(i_addr_record.r_city,'null')
AND nvl(state,'null') = nvl(i_addr_record.r_province,'null');
RETURN v_id_array;
end get_address_ids;
function send_addr_record
(
arr_addr IN addr_record_array
)
RETURN id_row_array
as
v_row_array id_row_array;
begin
for i in arr_addr.first..arr_addr.last loop
v_row_array(i).v_row_number := arr_addr(i).r_row_number;
v_row_array(i).v_id_array := get_address_ids(arr_addr(i));
end loop;
RETURN v_row_array;
end send_addr_record;
end address_array;
--Package body ADDRESS_ARRAY compiled.
===============
CALLING
===============
set serveroutput on;
declare
type address2_id_array is table of varchar2 (255);
type addr_record is record (
r_row_number varchar2 (255),
r_street_number varchar2 (255),
r_dirprefix varchar2 (255),
r_street_name varchar2 (255),
r_street_type varchar2 (255),
r_dirsuffix varchar2 (255),
r_suffix varchar2 (255),
r_city varchar2 (255),
r_province varchar2 (255)
);
type addr_record_array is table of addr_record;
v_addr_array addr_record_array;
type v_id_row is record (
v_row_number varchar2 (255),
v_id_array address2_id_array
);
type v_id_row_array is table of v_id_row;
v_addr_id_array v_id_row_array;
begin
for i in 1..5
loop
v_addr_array(i).r_row_number := to_char(i);
v_addr_array(i).r_street_number := to_char(i + 1000);
v_addr_array(i).r_dirprefix := null;
v_addr_array(i).r_street_name := 'RICHARDS';
v_addr_array(i).r_street_type := 'STREET';
v_addr_array(i).r_dirsuffix := null;
v_addr_array(i).r_suffix := null;
v_addr_array(i).r_city := 'VANCOUVER';
v_addr_array(i).r_province := 'BC';
end loop;
v_addr_id_array := ADDRESS_ARRAY.send_addr_record(v_addr_array);
for i in 1..5
loop
DBMS_OUTPUT.PUT_LINE(v_addr_id_array(i).v_row_number, v_addr_id_array(i).v_id_array(i));
end loop;
end;
Error report -
ORA-06550: line 42, column 20:
PLS-00306: wrong number or types of arguments in call to 'SEND_ADDR_RECORD'
ORA-06550: line 42, column 1:
PL/SQL: Statement ignored
ORA-06550: line 46, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 46, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Please help me solve the error.