Dear all,
Not sure this question should be post on PL/SQL or ORDS. Here is our environment: Oracle DB 19c / ORDS 21.2. For the below package with 2 procedures (overloaded), it can be compiled to valid on the database.
(1) When query with this URL, it will be okay.
https://<our domain>/package_test_y.proc?orgn_code=ABC&p_start_date=&p_end_date=
(2) When query with this URL (with 2 more parameters), it will be failed with: 500 Internal Server Error
https://<our domain>/package_test_y.proc?orgn_code=ABC&p_start_date=&p_end_date=&p_s_am_pm=&p_e_am_pm=
However, if I changed the package coding in either of the following ways. The second URL in above will also be successful.
- Swap the position of the first and second procedure: proc
- Align the data type of the parameters: ,p_s_am_pm / p_e_am_pm to be the same on 2 procedures.
Do you have an idea? Thanks.
>>>>>>>>>>>>>>>> Package code <<<<<<<<<<<<<<<<<
create or replace package package_test_y is
TYPE name_array_1 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
null_name_array_1 name_array_1;
/* First proc */
PROCEDURE proc
(
orgn_code VARCHAR2 DEFAULT NULL
,p_start_date name_array_1 DEFAULT null_name_array_1
,p_s_am_pm name_array_1 DEFAULT null_name_array_1
,p_end_date name_array_1 DEFAULT null_name_array_1
,p_e_am_pm name_array_1 DEFAULT null_name_array_1
);
/* Second proc */
PROCEDURE proc
(
p_id VARCHAR2
,orgn_code VARCHAR2 DEFAULT NULL
,p_s_am_pm VARCHAR2 DEFAULT NULL
,p_e_am_pm VARCHAR2 DEFAULT NULL
--,p_s_am_pm name_array_1 DEFAULT null_name_array_1
--,p_e_am_pm name_array_1 DEFAULT null_name_array_1
,p_start_date name_array_1 DEFAULT null_name_array_1
,p_end_date name_array_1 DEFAULT null_name_array_1
);
END;
/
CREATE OR REPLACE PACKAGE BODY package_test_y IS
/* First proc body */
PROCEDURE proc
(
orgn_code VARCHAR2 DEFAULT NULL
,p_start_date name_array_1 DEFAULT null_name_array_1
,p_s_am_pm name_array_1 DEFAULT null_name_array_1
,p_end_date name_array_1 DEFAULT null_name_array_1
,p_e_am_pm name_array_1 DEFAULT null_name_array_1
) IS
BEGIN
htp.print('First proc is called');
END proc;
/* Second proc body */
PROCEDURE proc
(
p_id VARCHAR2
,orgn_code VARCHAR2 DEFAULT NULL
,p_s_am_pm VARCHAR2 DEFAULT NULL
,p_e_am_pm VARCHAR2 DEFAULT NULL
--,p_s_am_pm name_array_1 DEFAULT null_name_array_1
--,p_e_am_pm name_array_1 DEFAULT null_name_array_1
,p_start_date name_array_1 DEFAULT null_name_array_1
,p_end_date name_array_1 DEFAULT null_name_array_1
) IS
BEGIN
htp.print('Second proc is called');
END proc;
end;