I have a PL/SQL program.
I want to execute it via HTTP(S).
What can I do?
Today I want to spend a few moments to explore your choices with Oracle REST Data Services (ORDS) when encountering this scenario. I’ve talked about it before, but repetition doesn’t hurt. I’ll even admit, it’s good practice for me!
With ORDS, we have two options:
I need a web service that lets me submit an employee’s ID, and returns me their name and phone number.
Luckily, our mature PL/SQL API already offers the back-end code to make this possible.
CREATE OR REPLACE PROCEDURE GET_CONTACT ( id_in IN NUMBER, firstname OUT VARCHAR2, lastname OUT VARCHAR2, phonenumber OUT VARCHAR2, countrycode OUT VARCHAR2 ) AS BEGIN SELECT first_name, last_name, phone_number, '1' INTO firstname, lastname, phonenumber, countrycode FROM employees WHERE employee_ID = id_in; EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END;
Things wrong with this code, including but not limited to:
- when others EXCEPTION handling, oh my…at least it’s not NULL
- hard-coding my input type definitions
- this code is for demo purposes only!
Let’s write some code
Our TEMPLATE will be ‘listing/:id’
Our HTTP verb will be ‘GET’
We’re going to return the output of the procedure in the Response Body.
So when we’re done, it will look a little something like this:
So how do we make this happen?
Well, we need to actually build up the code that will cause our PL/SQL to e executed. We need to have something for the output to be captured into, and we need to send that data on into the response body.
The solution could be as simple as this:
When you define your ORDS template, having something with a colon in the text, automatically results in local bind variables being made available to our SQL or PL/SQL blocks.
So if we had a template defined as /customer/:name/:salary/, then our handler code could immediately start referring to :name or :salary.
These are a form of explicit bind variables. They’re defined, but in the service template. The :body and :body_text variable for capturing Request bodies as BLOB or CLOBs are types of implicit bind variables – never actually declared, but we make them available to you.
In the above screenshot you’ll notice my DECLARE section is commented out. Since I’ve declared variables via the parameters section, I don’t need to declare local plsql variables to ‘catch’ my PL/SQL OUT values.
If you wanted to use local variables, you most definitely CAN. Just in your code block, be sure to assign a value to the Bind Parameters, e.g. ‘:cc := countrycode;’
Looking at the ‘Parameters’ section of our service definition, you’ll see we have four entries. The ‘Bind Parameter’ section is where we are saying what the bind variable names will be. And the ‘Name’ section is defining the JSON response attribute name.
So when I define ‘countrycode’ as an OUT RESPONSE STRING and map it to ‘cc’, I’m saying whatever :cc is equal to in the handler code block, return that in the response in a json document as ‘countrycode’.
You’ll notice the HTTP Response code is ‘200 OK.’ I’ve not set that explicitly, but the default for a successful ORDS request is in fact 200. If our PL/SQL code were to say…add a record to our phonebook collection, it would be better for us to set the Response to 201.
I talk about how to set Status Codes here, and how to handle PL/SQL exceptions with ‘nice’ response code and error messages to your REST API consumers here.
I’ve had to tell ORDS what I want to do with the results of my program. If I wanted to only send back one or a few vs all of the results, I could have done so. If i wanted to return them as Response Headers, that was also an option.
If I wanted to actually return HTML, I could have written some HTP calls to make that happen.
If I don’t want to write any code
There is also the Auto PL/SQL feature of ORDS. This is where you enable a stored procedure, and we publish a template with a POST handler backing it for you.
We automatically map the inputs and outputs for you.
That would look like this –
BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'HR', p_object => 'GET_CONTACT', p_object_type => 'PROCEDURE', p_object_alias => 'get_contact', p_auto_rest_auth => FALSE); COMMIT; END;
Notice I’m not declaring any variables. ORDS will look up what the inputs are – and expect them on your POST request body. ORDS will also figure out what the outputs are, and feed them to the POST response body.