Executing PL/SQL PACKAGE.PROCEDURE via REST

2021 Update: I originally wrote this post in 2018 – and since then I’ve taken on this type of question a few times. You might also enjoy these related posts, which are more technical/useful than a X in INT and Y out VARCHAR2 example like this:

• 4 ways to work with REF Cursors in your REST APIs
• ORDS 101 for PL/SQL

This from the YouTube:
“Hi Jeff, Can you please share similar example where REST service is calling a package.procedure with IN and OUT parameters and we can pass IN parameters to get the result?”

So, absolutely you can do this, and of course I’ll show you how. My solutions require Oracle REST Data Services (ORDS).

Before I show you HOW, let me show you the WHAT (our package.)

CREATE OR REPLACE PACKAGE rest_demo_in_out IS
    PROCEDURE demo (
        x     IN    INTEGER,
        y     OUT   VARCHAR2
    );

END rest_demo_in_out;
/

CREATE OR REPLACE PACKAGE BODY rest_demo_in_out AS

    PROCEDURE demo (
        x     IN    INTEGER,
        y     OUT   VARCHAR2
    ) AS
    -- take in a number, and returns it as a string
    -- if we get nothing in, we return a Zero
    BEGIN
        y   := 'X has been converted to a string, :  '
             || TO_CHAR(nvl(
            x,
            0
        ) );
        NULL;
    END demo;

END rest_demo_in_out;

As you can see, it’s a VERY simple procedure.

Ok, now how do we make this available via HTTPS?

ORDS: Auto PLSQL

ORDS is installed. HR is REST enabled.

Now, let’s enable our package for ORDS.

But wait, what does THAT mean?

We have an AUTO feature for tables, views, and PL/SQL. For tables and views, you get a full CRUD API to the data objects. For PL/SQL, you get a POST handler to execute the stored procedure, and we automatically grab the output and format it to JSON for the response.

You’ll want to alias the package, although I am not. You’ll also want to require authorization before putting this in a real application – assuming you’re not cool with ANYONE being able to execute our stored procedure.

If we did alias the package – by giving it an arbritary URI, then the consumers of our API wouldn’t actually know what the program is called in the database. You should only share the details that are required for the API users to use the API.

And WHEN we secure the service, only an authenticated user with the appropriate roles or privs granted will be able to access it.

Now’s let’s get on with using the API.

Here’s how.

1. It’s a POST
2. We have to send the IN parameter in on the POST body, using ‘Application/JSON’ mime type.
3. The URI will be /ords/hr/rest_demo_in_out/DEMO

And…GO!

This implementation of our API required ZERO additional code. We told ORDS to enable our package, and that’s it. We just have to send the POST REQUEST. But, if we don’t like how ORDS handles the scenario (inputs, outputs, status codes, links…), there’s not much we can do about it. Unless…unless you want to roll your OWN RESTful Service.

So let’s go do that now.

ORDS: RESTful Service

We’re going to create a custom RESTful Service, with a POST handler setup to run some PL/SQL…an anonymous block that runs our package for us.

You can find the full RESTful Service module defined below, but it looks like this –

Important things, not to miss:

1. Mime type on the Handler is set to application/json – that will let us grab the input parameter off of the POST body
2. ‘y’ is declared as a parameter for the handler anon block – that is used to pass the RESPONSE text back. I don’t have to call the variable OR the name ‘Y’, I’m just overloading it, so it’s obvious that it correlates to the ‘Y’ of the OUT parameter of the stored procedure we’re ultimately executing.
3. I don’t have to declare the Y parameter for my POST Handler block, but if I don’t, our POST response will just be a 200, without the output of the procedure attached.

Let’s run it:

In our run it once & see how it goes scnearios, you can see that the RESTful Service is executing a good bit faster than the Auto method. ORDS has more work to do on a AUTO call:

• look-up the plsql program
• figure out the inputs
• figure out the outputs…

… whereas the RESTful Services are statically defined in the database. ORDS knows exactly what to do.

While you should take care to test your scenarios under load to make sure they’re adequately performant, note that in an upcoming release this year, ORDS will cache the service definitions for both AUTO and Modules based APIs, so these will get much faster, esp under load.

Also, you’ll note I’ve done all of this REST stuff with Database Actions (SQL Developer Web). You don’t HAVE to use tools if you don’t want to. ORDS has a PL/SQL API, you can just use via and SQLcl if you want.

This would actually be preferred way of deploying your code to PROD, via automated CI/CD processes, not pointing and clicking on things manually…

The RESTful Service Code

This will REST enable the schema and publish the RESTful Service for executing our Stored Procedure.

-- Generated by Oracle SQL Developer REST Data Services 18.3.0.236.0501
-- Exported REST Definitions from ORDS Schema Version 18.2.0.r1831332
-- Schema: HR   Date: Sun Aug 26 10:54:01 EDT 2018
--
BEGIN
    ords.enable_schema(
        p_enabled               => true,
        p_schema                => 'HR',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'hr',
        p_auto_rest_auth        => false
    );

    ords.define_module(
        p_module_name      => 'youtube',
        p_base_path        => '/youtube/',
        p_items_per_page   => 25,
        p_status           => 'PUBLISHED',
        p_comments         => NULL
    );

    ords.define_template(
        p_module_name   => 'youtube',
        p_pattern       => 'pack_proc',
        p_priority      => 0,
        p_etag_type     => 'HASH',
        p_etag_query    => NULL,
        p_comments      => NULL
    );

    ords.define_handler(
        p_module_name      => 'youtube',
        p_pattern          => 'pack_proc',
        p_method           => 'POST',
        p_source_type      => 'plsql/block',
        p_items_per_page   => 0,
        p_mimes_allowed    => 'application/json',
        p_comments         => NULL,
        p_source           => 'DECLARE
  comes_out VARCHAR2(200);
BEGIN

  REST_DEMO_IN_OUT.DEMO(
    X => :goes_in,
    Y => comes_out
  );

 :y := comes_out;
END;'
    );

    ords.define_parameter(
        p_module_name          => 'youtube',
        p_pattern              => 'pack_proc',
        p_method               => 'POST',
        p_name                 => 'y',
        p_bind_variable_name   => 'y',
        p_source_type          => 'RESPONSE',
        p_param_type           => 'STRING',
        p_access_method        => 'OUT',
        p_comments             => NULL
    );

    COMMIT;
END;

Umm, what about Package Functions?

Yup, here you go.