Example: ORDS and AutoPLSQL with Packages

Updated 27 May 2025

In addition to providing AutoREST features for Oracle Database Tables and Views, ORDS also supports publishing endpoints to access your PL/SQL programs. This would be akin to Remote Procedure Call (rpc) vs a REST API. Our RPC mechanism is largely the same as REST, you make a HTTPS POST request, and we call your program, and return any database output as JSON.

I previously discussed this feature, but focused on standalone procedures and functions.

Let’s build our example.

So assuming you have HR installed somewhere and HR is REST enabled, let’s build this package.

create or replace package rpc as
   procedure give_raises (
      increase    in number,
      total_payroll out integer,
      highest_emp out sys_refcursor
   );

   function managers (
      dept in integer
   ) return sys_refcursor;

   procedure emp_json (id in integer, output out json);

end rpc;
/

create or replace package body rpc as

   procedure give_raises (
      increase    in number,
      total_payroll out integer,
      highest_emp out sys_refcursor
   ) as
   begin
      update hr.employees
         set
         salary = salary + (salary * increase);

      commit;
      select sum(salary)
        into total_payroll
        from hr.employees;

      open highest_emp for select first_name,
                                  last_name,
                                  salary,
                                  commission_pct
                             from hr.employees
                            order by salary desc nulls last 
                            fetch first 1 rows only;

   end give_raises;

   function managers (
      dept in integer
   ) return sys_refcursor as
      d sys_refcursor;
   begin
      open d for select *
                              from hr.employees
                  where employee_id in (
                    select distinct manager_id
                      from hr.employees
                 )
                    and department_id = dept;

      return d;
   end managers;

   procedure private_proc as
   begin
      null;
   end private_proc;

   procedure emp_json (
      id     in integer,
      output out json
   ) as
   begin
      select
         json_object(
            'Name' value first_name
                         || ' '
                         || last_name,
                     'Salary' value salary,
                     'hasCommission' value
               case
                  when commission_pct is null then
                     'false'
                  else
                     'true'
               end
         format json)
        into output
        from employees
       where employee_id = id;
   end emp_json;

end rpc;
/

And let’s ORDS enable our Package. We can do this with our ORDS PL/SQL API, or we can use SQL Developer Classic or SQL Developer Web via ‘right clicks’ – and soon we’ll support this in our VS Code Extension.

The ORDS PL/SQL API has an ENABLE_OBJECT option, you can use this for tables, views, and PL/SQL (procedures, functions, and packages).

begin
   ords.enable_object(
      p_enabled        => true,
      p_schema         => 'HR',
      p_object         => 'RPC',
      p_object_type    => 'PACKAGE',
      p_object_alias   => 'rpc',
      p_auto_rest_auth => false
   );
   commit;
end;
/

After ORDS is told about the package, it will make it’s functions and procedures available.

And now we have access to these POST Handlers.

Special Note: You’ll notice the patterns are UPPERCASE. If you try /resty/give_raises, you’ll get a 404.

Special Note #2: You’ll finally notice that the procedure PRIVATE_PROC is not listed..because, it’s not declared in the package spec, private programs aren’t supported.

Special Note #3: Overloaded PACKAGE members are not supported with the AUTO feature, you’ll need to roll your own RESTful Services to invoke those.

Executing our programs

The easiest way to play with these new package programs is via the built-in OpenAPI Swagger tooling built into SQL Developer Web.

If I navigate to the AUTO section and the Packages, I can ask for the OpenAPI page.

Try it Out

For each of the POST handlers, we can simply, ‘try it out.’

Our program finds the EMPLOYEE record for the manager of a given department and returns it as an OUT SYS_REFCURSOR. ORDS takes this and converts it to a JSON object.

Let’s try another one!

GIVE_RAISES is a fun one. We take a number IN, and use that to give folks a raise, plus return the new total payroll for the company and the highest paid employee.

We can use the OpenAPI doc page to generate cURL for us to use to test the endpoint.

curl -X 'POST' \
  'http://localhost:8181/ords/hr/rpc/GIVE_RAISES' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "increase": 0.05
}' | jq

Note I have the jq utility installed and I’m piping the output from cURL thru that so I can get pretty, human-readable JSON out.

Running that on my machine gives me this:

{
  "total_payroll": 85354,
  "highest_emp": [
    {
      "first_name": "Guy",
      "last_name": "Himuro",
      "salary": 3009.83,
      "commission_pct": null
    }
  ]
}

One more!

The last procedure, EMP_JSON grabs an employee record and constructs a custom JSON object using the JSON_OBJECT function. The procedure has an OUT JSON parameter defined, so ORDS knows that JSON is being returned.

Let’s call this one as well, also via cURL (generated for me via the OpenAPI page in SQL Developer Web):

curl -X 'POST' \
  'http://localhost:8181/ords/hr/rpc/EMP_JSON' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "id": 110
}' | jq

And the response –

{
  "output": {
    "Name": "John Chen",
    "Salary": 949.25,
    "hasCommission": false
  }
}

A few notes:

• the out parameter is called ‘output’ in the procedure declaration, reflected in the response
• ORDS doesn’t attempt to convert the output to JSON, as it knows it’s already dealing with JSON – notice the InitCap and CamelCast formatting

One Final Word

This feature is here to help you get your PL/SQL available via HTTP. It’s raison d’etre?

The sole intent for Auto Pl/SQL is for translating plsql in/out into binds.

This means if you’re doing something outside of passing data to PL/SQL and getting it back, or sending it on somewhere else…like say printing via HTP.PRN(), then you need to use a custom RESTful Service.

One Final, Final, Final Word

Overloading procedures in packages aren’t supported, yet.

If you’re using TYPES in your plsql programs for INPUT or OUTPUT, they need to be standalone, user defined types in order for ORDS to be able to work with them.

The AUTO feature will be slightly slower than calling your own SQL or PL/SQL based ORDS module. This is because we’re running queries/code in order to determine the package members and their inputs and outputs. You can mitigate this largely by taking advantage of our ORDS metadata caching feature.