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:

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.

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.

What you should be doing…

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!

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

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 –

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:

We’re passing ‘:goes_in’ => X, telling ORDS to use JSON attribute called goes_in from request body.

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.

HTTPie is like cURL, but much easier to use.
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Avatar
    NagaShankar Reply

    Hi Jeff Smith

    Thanks for sharing your knowledge to us through this site.. Its very helpful.
    I have a doubt about the objects created in ORDS.
    I noticed that ORDS has the feature of creating objects in the “SQL Workshop” tab, including PL/SQL packages. I created a sample PL/SQL package successfully which has the capability to create an Order in Oracle Applications(as my ords is configured over Oracle ERP Applications). But my doubt here is “can we expose this PL/SQL object as REST service? If so how can this be done?” Request you to please provide your valuable inputs in this case.

    Thanks In Advance
    NagaShankar

    • thatjeffsmith

      You have two options.

      You can build a restful service that calls your plsql code, or you can use the Auto plsql feature. I have blog posts showing both.

  2. Avatar

    Hi Jeff,

    I created the package and rest enabled it from SQL Developer , it works absolutely fine.

    But when I have tried the same piece of code just to test the package calling from ORDS with PL/SQL API’s , I am keep getting
    {
    “Y”: “X has been converted to a string, : 0”
    }
    as output .
    My input json {“x”: 1234}
    Seems like its unable to read value of x here , can you please help. here is my code –

    BEGIN

    ords.define_module(
    p_module_name => ‘JNPROCMOD’,
    p_base_path => ‘/JNPROC/’,
    p_items_per_page => 25,
    p_status => ‘PUBLISHED’,
    p_comments => NULL
    );

    ords.define_template(
    p_module_name => ‘JNPROCMOD’,
    p_pattern => ‘pack_proc’,
    p_priority => 0,
    p_etag_type => ‘HASH’,
    p_etag_query => NULL,
    p_comments => NULL
    );

    ords.define_handler(
    p_module_name => ‘JNPROCMOD’,
    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
    jn_out VARCHAR2(200);
    BEGIN

    REST_DEMO_IN_OUT.DEMO(
    X => :jn_in,
    Y => jn_out
    );

    :y := jn_out;
    END;’
    );

    ords.define_parameter(
    p_module_name => ‘JNPROCMOD’,
    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;

    • Avatar
      Phil Goldenberg

      I had the same issue. My first fix was to put my equivalent of “jn_in” instead of “x” into the json input, though i think that might be not be great because it exposes the bind variable(?). The better way may be to leave the x in the json input, but go to the Parameters tab of your API in SQL Developer and add an entry for x, with “jn_in” (no colon) in the Bind Parameter field, “IN” in Access Method, “URl” in Source Type, “Integer” in Data Type.
      Be careful with case sensitivity too, whatever you name the parameter has to match what’s in your JSON input exactly.

Write A Comment

RSS
Follow by Email
LinkedIn
Share