ORDS 101: Working with PL/SQL

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:

Very little code, or as much code as you need.

Our Example

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:

a GET on /listing/101 will grab me the details on employee number 101.

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:

The bind variables here are doing most of the work.

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

It’s up to me what this {json} will look like…or up to my code at least.

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.