Executing PL/SQL PACKAGE.PROCEDURE via REST

thatjeffsmith ORDS 2 Comments

Tell Others About This Story:

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.
  4. 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.

    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;

    Related Posts

    Tell Others About This Story:

Comments 2

  1. 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;

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *