We are using ORDS as a Proof of Concept for a future feature. ORDS and the SQL queries work properly when the parameters needed are taken from the HTTP Headers, or as a form of URI parameters. The issue is, a stored procedure is not executed properly when it's called using a defined handler. We have run the mentioned procedure stand-alone as well as called the procedure from a different SQL file, both giving the proper results. For reference, we are doing the testing on an EMPLOYEE table.
This is the code that we are using:
--THIS IS THE SQL WORKSHEET IN THE ORDS MODULE
--The method is POST
BEGIN
reporterror(160523,'inside post exception');
ORDS.define_handler(
p_module_name => 'EmpTest',
p_pattern => 'add/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => 'BEGIN user1.empadd(emp_id => :emp_id, new_emp_id => :new_emp_id); END;',
p_items_per_page => 0);
COMMIT;
reporterror(160523,'inside begin');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
reporterror(160523,'inside post exception');
COMMIT;
END;
What we are trying to achieve here is to call the procedure, and the parameters for further use are in the JSON body sent when the URL is hit. If it helps, the URL is hit via Postman. URL and body are mentioned at the end.
In case it is required, the stored procedure that we are using:
create or replace procedure empadd(
emp_id IN NUMBER,
new_emp_id IN NUMBER
)
AS
BEGIN
reporterror(160523, 'Inside EMPADD, before procedure call');
UPDATE NETCONF.EMPLOYEE
SET EMPID = new_emp_id
where EMPID = emp_id;
reporterror(160523,'EMPLOYEE ID--' || emp_id);
COMMIT;
END empadd;
In the procedures, reporterror() is something that logs the error in a different table. We use it for debugging purposes.
The URLs and the ORDS access, along with the DB permissions for executions of the ORDS module and stored procedures have been checked, and are proper.
Just for reference, the URL:
<base url>/ords/netconf/emp/add
and the body of the URL is:
{ "emp_id": 1, "new_emp_id": 50 }
Please let me know if any information is missing or required. Any help in making this work is appreciated.