I am trying to get the json response using the ref cursor but it is returning error. Main Procedure:
CREATE OR REPLACE PROCEDURE GET_EMPLOYEE_ALL1(p_emp_no IN test_dec.emp_no%type,p_emp_output OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_emp_output FOR select * from test_dec where emp_no=p_emp_no;
END GET_EMPLOYEE_ALL1;
Handler and Parameter is defined as under.
BEGIN
ords.define_template(p_module_name => 'rest-v1',
p_pattern => 'employee/get_employee_all/');
ords.define_handler(p_module_name => 'rest-v1',
p_pattern =>'employee/get_employee_all/',
p_method =>'POST',
p_source_type =>ORDS.source_type_plsql,
p_mimes_allowed => 'application/json',
p_source =>'DECLARE emp_detail SYS_REFCURSOR; BEGIN rest_package.get_employee_all(p_emp_no => :emp_no,p_emp_output => :emp_detail); p_emp_output := :emp_detail; END;',
p_items_per_page =>0);
COMMIT;
END;
BEGIN
ORDS.define_parameter(
p_module_name => 'rest-v1',
p_pattern => 'employee/get_employee_all/',
p_method => 'POST',
p_name => 'emp_detail',
p_bind_variable_name => 'emp_detail',
p_source_type => 'RESPONSE',
p_param_type => 'RESULTSET',
p_access_method => 'OUT'
);
END;
While running from POSTMAN I am getting and 403 Erorr: The request could not be processed because a function or procedure referenced by the PL/SQL statement being evaluated is not accessible or does not exist
Here's a procedure querying EMPLOYEES and DEPARTMENTS, accepting an INPUT parameter, which is fed to the SQL being executed for the cursor.
Here's a RESTful Web Service built with ORDS that executes said procedure.
Built into the URI is a :bind for sending the input to the procedure. I've defined a parameter to catch the results of the refcursor. The parameter is defined to be an OUT Response of type 'ResultSet'.
Now I want to call the RESTful Service -
Here's the complete response, note it's the entirety of the refcursor being returned. If you want paging, you'll need to build that into your pl/sql logic.