ORDS Executing PL/SQL PACKAGE.PROCEDURE via REST

I don’t normally blog on a Sunday, but I’m confined to my bed/couch for the next few days, so I might as well answer another question!

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.

Right-click on the package, and select ‘Enable REST Service.’

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.

Now we can call it.

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!

Note the RESPONSE is automatically generated based off the OUT parameter.

That solution basically required ZERO code. We told ORDS to handle our package, and it does. We just have to send the POST REQUEST. But, if we don’t like how ORDS handles the scenario, 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 –

Very basic, but a few important things not to miss!

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:



Here’s also how to pass a ‘NULL’ to our Stored Procedure.

In our informal testing, it appears that the RESTful Service is executing a good bit faster than the Auto method. ORDS has more work to do on a AUTO call, whereas the RESTful SErvices are more static in nature, so this kind of makes sense to me. But you should take care to test your scenarios under load to make sure they’re adequately performant.
Also, you’ll note I’ve done all of this REST stuff with SQL Developer. You don’t HAVE to use SQL Developer. ORDS has a PL/SQL API, you can just use via and SQL*Plus if you want. I just can’t see how you’d WANT to do that.