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
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.
Now we can call it.
- It’s a POST
- We have to send the IN parameter in on the POST body, using ‘Application/JSON’ mime type.
- The URI will be /ords/hr/rest_demo_in_out/DEMO
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 –
Important things, not to miss:
- Mime type on the Handler is set to application/json – that will let us grab the input parameter off of the POST body
- ‘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.
- 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 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 22.214.171.124.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;