How to Call PL/SQL Procedures and Functions with AutoREST

AutoREST is not only for tables. A PL/SQL function, procedure, or package can be REST-enabled the same way, and ORDS then runs it for a POST request: JSON fields become the parameters, and the result comes back as JSON. This guide publishes a function, a procedure with OUT parameters, and a package function.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

Syntax

ords.enable_object(p_enabled        => true,
                   p_schema         => 'SCHEMA',
                   p_object         => 'OBJECT_NAME',
                   p_object_type    => 'FUNCTION',      -- or PROCEDURE, PACKAGE
                   p_object_alias   => 'alias',
                   p_auto_rest_auth => false);

POST .../alias/                   -- a function or procedure
POST .../alias/SUBPROGRAM_NAME    -- a subprogram of a package

The request body is a JSON object with the parameter names as keys.

Publish a Function and a Procedure

FARE_WITH_TAX adds tax to a fare, with a default tax rate. ROUTE_STATS returns the number of routes from an airport and the longest one, in OUT parameters.

Example:

create or replace function fare_with_tax (p_fare number, p_tax_pct number default 5)
  return number is
begin
  return round(p_fare * (1 + p_tax_pct / 100), 2);
end;
/
create or replace procedure route_stats (p_origin   in  varchar2,
                                         p_routes   out number,
                                         p_longest  out number) is
begin
  select count(*), max(distance_km) into p_routes, p_longest
  from   routes where origin = upper(p_origin);
end;
/
begin
  ords.enable_object(p_enabled => true, p_schema => 'NIMBUS', p_object => 'FARE_WITH_TAX',
                     p_object_type => 'FUNCTION', p_object_alias => 'fare_with_tax',
                     p_auto_rest_auth => false);
  ords.enable_object(p_enabled => true, p_schema => 'NIMBUS', p_object => 'ROUTE_STATS',
                     p_object_type => 'PROCEDURE', p_object_alias => 'route_stats',
                     p_auto_rest_auth => false);
  commit;
end;
/

Output:

Function FARE_WITH_TAX compiled

Procedure ROUTE_STATS compiled

PL/SQL procedure successfully completed.

Call the Function

Example:

curl -k -X POST https://localhost:8443/ords/nimbus/fare_with_tax/ \
  -H "Content-Type: application/json" \
  -d '{"p_fare":520,"p_tax_pct":8}'

Output:

{
    "~ret": 561.6
}

A function's return value comes back as ~ret. Leaving out p_tax_pct uses the default of 5 percent, and the result is 546.

Call the Procedure

Example:

curl -k -X POST https://localhost:8443/ords/nimbus/route_stats/ \
  -H "Content-Type: application/json" \
  -d '{"p_origin":"dxb"}'

Output:

{
    "p_routes": 20,
    "p_longest": 14200
}

The OUT parameters come back by name. Only POST is accepted: a GET on the same URL returns 405 Method Not Allowed.

Call a Package Function

Example:

create or replace package fare_tools as
  function in_aed (p_usd number) return number;
end;
/
create or replace package body fare_tools as
  function in_aed (p_usd number) return number is
  begin
    return round(p_usd * 3.6725, 2);
  end;
end;
/
begin
  ords.enable_object(p_enabled => true, p_schema => 'NIMBUS', p_object => 'FARE_TOOLS',
                     p_object_type => 'PACKAGE', p_object_alias => 'fare_tools',
                     p_auto_rest_auth => false);
  commit;
end;
/

Example:

curl -k -X POST https://localhost:8443/ords/nimbus/fare_tools/IN_AED \
  -H "Content-Type: application/json" \
  -d '{"p_usd":520}'

Output:

{
    "~ret": 1909.7
}

The subprogram name in the URL is case-sensitive: /fare_tools/in_aed in lowercase returned 404 Not Found.

Things to Know

• Enabling a package publishes all of its public subprograms; enable a wrapper package to expose only some.
• Every call runs as the schema owner, so a published procedure can do anything the schema can; protect such endpoints.
• For a URL design of your own, such as GET with query parameters, call the PL/SQL from a REST module handler instead.

Related Guides

• How to REST-Enable a Table with ORDS AutoREST
• How to Write a POST Handler That Inserts Rows in ORDS

Conclusion

AutoREST publishes PL/SQL functions, procedures, and packages as POST endpoints: parameters go in as JSON, return values come back as ~ret, and OUT parameters by name. Remember that package subprogram names in URLs are case-sensitive, and protect anything that changes data.