Stack Exchange
ORDS: How to retrieve json response using refcursor in plsql ords
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.
CREATE OR REPLACE PROCEDURE GET_EMPS_SALS_REST(MIN_SAL IN hr.employees.salary%type, p_emps_output OUT SYS_REFCURSOR) IS 
   BEGIN 
       OPEN p_emps_output FOR select FIRST_NAME
       || ' '
       || LAST_NAME EmployeeName
     , SALARY
     , DEPARTMENT_NAME
  from DEPARTMENTS
  join EMPLOYEES on DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
 where salary > MIN_SAL
 order by DEPARTMENT_NAME
        , SALARY desc;
   END GET_EMPS_SALS_REST;
/

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'.
-- Generated by ORDS REST Data Services 20.4.0.b3391848
-- Schema: HR  Date: Fri Dec 11 11:36:17 2020 
--

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'stackoverflow.answers',
      p_base_path      => '/so/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'things i code to help answer questions on StackOverflow');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'stackoverflow.answers',
      p_pattern        => 'plsql_refcur/:input',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'execute a stored proc returning a refcursor');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'stackoverflow.answers',
      p_pattern        => 'plsql_refcur/:input',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page => 0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
 get_emps_sals_rest(:input, :results);
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'stackoverflow.answers',
      p_pattern            => 'plsql_refcur/:input',
      p_method             => 'POST',
      p_name               => 'emps_sal_by_dept',
      p_bind_variable_name => 'results',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  
COMMIT;

END;

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.
{
  "emps_sal_by_dept": [
    {
      "employeename": "Shelley Higgins",
      "salary": 12000,
      "department_name": "Accounting"
    },
    {
      "employeename": "William Gietz",
      "salary": 8300,
      "department_name": "Accounting"
    },
    {
      "employeename": "Jennifer Whalen",
      "salary": 4400,
      "department_name": "Administration"
    },
    {
      "employeename": "Steven King",
      "salary": 24000,
      "department_name": "Executive"
    },
    {
      "employeename": "Neena Kochhar",
      "salary": 21250,
      "department_name": "Executive"
    },
    {
      "employeename": "Lex De Haan",
      "salary": 17000,
      "department_name": "Executive"
    },
    {
      "employeename": "Nancy Greenberg",
      "salary": 12000,
      "department_name": "Finance"
    },
    {
      "employeename": "Daniel Faviet",
      "salary": 9000,
      "department_name": "Finance"
    },
    {
      "employeename": "John Chen",
      "salary": 8200,
      "department_name": "Finance"
    },
    {
      "employeename": "Jose Manuel Urman",
      "salary": 7800,
      "department_name": "Finance"
    },
    {
      "employeename": "Ismael Sciarra",
      "salary": 7700,
      "department_name": "Finance"
    },
    {
      "employeename": "Luis Popp",
      "salary": 6900,
      "department_name": "Finance"
    },
    {
      "employeename": "Susan Mavris",
      "salary": 6500,
      "department_name": "Human Resources"
    },
    {
      "employeename": "Alexander Hunold",
      "salary": 9000,
      "department_name": "IT"
    },
    {
      "employeename": "Bruce Ernst",
      "salary": 6000,
      "department_name": "IT"
    },
    {
      "employeename": "David Austin",
      "salary": 4800,
      "department_name": "IT"
    },
    {
      "employeename": "Valli Pataballa",
      "salary": 4800,
      "department_name": "IT"
    },
    {
      "employeename": "Diana Lorentz",
      "salary": 4200,
      "department_name": "IT"
    },
    {
      "employeename": "Michael Hartstein",
      "salary": 13000,
      "department_name": "Marketing"
    },
    {
      "employeename": "Pat Fay",
      "salary": 6000,
      "department_name": "Marketing"
    },
    {
      "employeename": "Hermann Baer",
      "salary": 10000,
      "department_name": "Public Relations"
    },
    {
      "employeename": "Den Raphaely",
      "salary": 11000,
      "department_name": "Purchasing"
    },
    {
      "employeename": "Alexander Khoo",
      "salary": 3100,
      "department_name": "Purchasing"
    },
    {
      "employeename": "Shelli Baida",
      "salary": 2900,
      "department_name": "Purchasing"
    },
    {
      "employeename": "Sigal Tobias",
      "salary": 2800,
      "department_name": "Purchasing"
    },
    {
      "employeename": "Guy Himuro",
      "salary": 2600,
      "department_name": "Purchasing"
    },
    {
      "employeename": "John Russell",
      "salary": 14000,
      "department_name": "Sales"
    },
    {
      "employeename": "Karen Partners",
      "salary": 13500,
      "department_name": "Sales"
    },
    {
      "employeename": "Alberto Errazuriz",
      "salary": 12000,
      "department_name": "Sales"
    },
    {
      "employeename": "Lisa Ozer",
      "salary": 11500,
      "department_name": "Sales"
    },
    {
      "employeename": "Gerald Cambrault",
      "salary": 11000,
      "department_name": "Sales"
    },
    {
      "employeename": "Ellen Abel",
      "salary": 11000,
      "department_name": "Sales"
    },
    {
      "employeename": "Eleni Zlotkey",
      "salary": 10500,
      "department_name": "Sales"
    },
    {
      "employeename": "Clara Vishney",
      "salary": 10500,
      "department_name": "Sales"
    },
    {
      "employeename": "Harrison Bloom",
      "salary": 10000,
      "department_name": "Sales"
    },
    {
      "employeename": "Janette King",
      "salary": 10000,
      "department_name": "Sales"
    },
    {
      "employeename": "Peter Tucker",
      "salary": 10000,
      "department_name": "Sales"
    },
    {
      "employeename": "Tayler Fox",
      "salary": 9600,
      "department_name": "Sales"
    },
    {
      "employeename": "Danielle Greene",
      "salary": 9500,
      "department_name": "Sales"
    },
    {
      "employeename": "Patrick Sully",
      "salary": 9500,
      "department_name": "Sales"
    },
    {
      "employeename": "David Bernstein",
      "salary": 9500,
      "department_name": "Sales"
    },
    {
      "employeename": "Peter Hall",
      "salary": 9000,
      "department_name": "Sales"
    },
    {
      "employeename": "Allan McEwen",
      "salary": 9000,
      "department_name": "Sales"
    },
    {
      "employeename": "Alyssa Hutton",
      "salary": 8800,
      "department_name": "Sales"
    },
    {
      "employeename": "Jonathon Taylor",
      "salary": 8600,
      "department_name": "Sales"
    },
    {
      "employeename": "Jack Livingston",
      "salary": 8400,
      "department_name": "Sales"
    },
    {
      "employeename": "Lindsey Smith",
      "salary": 8000,
      "department_name": "Sales"
    },
    {
      "employeename": "Christopher Olsen",
      "salary": 8000,
      "department_name": "Sales"
    },
    {
      "employeename": "Nanette Cambrault",
      "salary": 7500,
      "department_name": "Sales"
    },
    {
      "employeename": "Louise Doran",
      "salary": 7500,
      "department_name": "Sales"
    },
    {
      "employeename": "William Smith",
      "salary": 7400,
      "department_name": "Sales"
    },
    {
      "employeename": "Elizabeth Bates",
      "salary": 7300,
      "department_name": "Sales"
    },
    {
      "employeename": "Mattea Marvins",
      "salary": 7200,
      "department_name": "Sales"
    },
    {
      "employeename": "Sarath Sewall",
      "salary": 7000,
      "department_name": "Sales"
    },
    {
      "employeename": "Oliver Tuvault",
      "salary": 7000,
      "department_name": "Sales"
    },
    {
      "employeename": "David Lee",
      "salary": 6800,
      "department_name": "Sales"
    },
    {
      "employeename": "Sundar Ande",
      "salary": 6400,
      "department_name": "Sales"
    },
    {
      "employeename": "Amit Banda",
      "salary": 6200,
      "department_name": "Sales"
    },
    {
      "employeename": "Charles Johnson",
      "salary": 6200,
      "department_name": "Sales"
    },
    {
      "employeename": "Sundita Kumar",
      "salary": 6100,
      "department_name": "Sales"
    },
    {
      "employeename": "Adam Fripp",
      "salary": 8200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Matthew Weiss",
      "salary": 8000,
      "department_name": "Shipping"
    },
    {
      "employeename": "Payam Kaufling",
      "salary": 7900,
      "department_name": "Shipping"
    },
    {
      "employeename": "Shanta Vollman",
      "salary": 6500,
      "department_name": "Shipping"
    },
    {
      "employeename": "Kevin Mourgos",
      "salary": 5800,
      "department_name": "Shipping"
    },
    {
      "employeename": "Nandita Sarchand",
      "salary": 4200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Alexis Bull",
      "salary": 4100,
      "department_name": "Shipping"
    },
    {
      "employeename": "Sarah Bell",
      "salary": 4000,
      "department_name": "Shipping"
    },
    {
      "employeename": "Britney Everett",
      "salary": 3900,
      "department_name": "Shipping"
    },
    {
      "employeename": "Kelly Chung",
      "salary": 3800,
      "department_name": "Shipping"
    },
    {
      "employeename": "Renske Ladwig",
      "salary": 3600,
      "department_name": "Shipping"
    },
    {
      "employeename": "Jennifer Dilly",
      "salary": 3600,
      "department_name": "Shipping"
    },
    {
      "employeename": "Trenna Rajs",
      "salary": 3500,
      "department_name": "Shipping"
    },
    {
      "employeename": "Julia Dellinger",
      "salary": 3400,
      "department_name": "Shipping"
    },
    {
      "employeename": "Laura Bissot",
      "salary": 3300,
      "department_name": "Shipping"
    },
    {
      "employeename": "Jason Mallin",
      "salary": 3300,
      "department_name": "Shipping"
    },
    {
      "employeename": "Stephen Stiles",
      "salary": 3200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Julia Nayer",
      "salary": 3200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Winston Taylor",
      "salary": 3200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Samuel McCain",
      "salary": 3200,
      "department_name": "Shipping"
    },
    {
      "employeename": "Alana Walsh",
      "salary": 3100,
      "department_name": "Shipping"
    },
    {
      "employeename": "Curtis Davies",
      "salary": 3100,
      "department_name": "Shipping"
    },
    {
      "employeename": "Jean Fleaur",
      "salary": 3100,
      "department_name": "Shipping"
    },
    {
      "employeename": "Kevin Feeney",
      "salary": 3000,
      "department_name": "Shipping"
    },
    {
      "employeename": "Anthony Cabrio",
      "salary": 3000,
      "department_name": "Shipping"
    },
    {
      "employeename": "Michael Rogers",
      "salary": 2900,
      "department_name": "Shipping"
    },
    {
      "employeename": "Timothy Gates",
      "salary": 2900,
      "department_name": "Shipping"
    },
    {
      "employeename": "Mozhe Atkinson",
      "salary": 2800,
      "department_name": "Shipping"
    },
    {
      "employeename": "Girard Geoni",
      "salary": 2800,
      "department_name": "Shipping"
    },
    {
      "employeename": "Vance Jones",
      "salary": 2800,
      "department_name": "Shipping"
    },
    {
      "employeename": "Irene Mikkilineni",
      "salary": 2700,
      "department_name": "Shipping"
    },
    {
      "employeename": "John Seo",
      "salary": 2700,
      "department_name": "Shipping"
    },
    {
      "employeename": "Douglas Grant",
      "salary": 2600,
      "department_name": "Shipping"
    },
    {
      "employeename": "Randall Matos",
      "salary": 2600,
      "department_name": "Shipping"
    },
    {
      "employeename": "Donald OConnell",
      "salary": 2600,
      "department_name": "Shipping"
    }
  ]
}

