How To: Oracle POST API..Run SQL..Retrieve JSON Results

Running SQL via a HTTPS GET request is about as straight-forward as it comes when building REST APIs with Oracle REST Data Services (ORDS.)

GET on queries/emps/ – returns a JSON document.

But I’ve recently been asked more than a few times on how you would go about doing this for a POST. Sometimes all I’m told is “Our requirement is to do this via a POST.”

Maybe you need to include a ton of information on the request via the BODY…that’s not available for GET requests. Or maybe your vendor/partner/app/whatever just WANTS a POST.

I’ll show you 3 possible ways of doing this:

SELECT INTO and ORDS Parameters
REFCursor and ORDS Parameter
Generate your own response and print it

I’ve covered this topic before, but I limited myself to doing it via PL/SQL REFCURSORs. If you know that’s the way you wan to go, see this POST…post.

4 Ways to work with ORDS and PL/SQL REFCursors

Let’s build a REST API via POST that runs SQL

Puts on dance/tap shoes…

Method One: PL/SQL with SELECT INTO

So OK, we’ll create a new template

queries/emps/:id

And we’ll attach a POST handler.

What code do we want to run? Well, that’s kind of up to you. What are you trying to do with the API? Let’s say we just want to build a simple JSON document based on a single row query result, or in the RESTful parlance, retrieving an item from a collection.

My POST handler has to be implemented via PL/SQL, but one of the best things about PL/SQL, is it’s ability to run SQL 🙂

Let’s get an employee, based on their EMPLOYEE_ID.

SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 101;
 
...
PLS-00428: an INTO clause IS expected IN this SELECT statement

This works in SQL, but fails in PL/SQL – your program is expected to so something with the output of that query.

We’re going to pick a few of the columns, and SELECT INTO some variables.

BEGIN
   SELECT FIRST_NAME
          || ' '
          || LAST_NAME,
          EMAIL,
          PHONE_NUMBER,
          TO_CHAR(HIRE_DATE,'MON-DD-YYYY') start_date,
          JOB_ID
     INTO
      :name,
      :mail,
      :phone,
      :first_day,
      :job
     FROM hrrest.employees
     WHERE employee_id = :id;
END;

Those look like bind variables.

That’s because they ARE bind variables!

But where are they defined? Normally you’d have a declare section up top with name, mail, phone declared as something like EMPLOYEES.EMAIL%TYPE.

Instead, we’re going to create some ORDS Handler Parameters.

Here is where we define both our :binds and our ORDS Response JSON attributes.

By having a parameter of type ‘Response’, we’re telling ORDS to take some data and put it into the JSON body of the HTTP Response.

See that ‘Show Code’ toggle at the bottom?

It’ll show us the ORDS PL/SQL package call behind creating, or DEFINE(ing) a parameter.

-- DEFINE PARAMETER
BEGIN
    ORDS.DEFINE_PARAMETER(
        p_module_name        => '101',
        p_pattern            => 'queries/emps/:id',
        p_method             => 'POST',
        p_name               => 'employee_name',
        p_bind_variable_name => 'name',
        p_source_type        => 'RESPONSE',
        p_access_method      => 'OUT',
        p_comments           => ''FIRST AND LAST name OF the employee
 {"employee_name" : "Jeff Smith"}''
    );
    COMMIT;
END;

We now need to repeat the process for each column, assuming we want them all to come back to the API consumer.

When you’re done, you should have something that looks like this –

The names and bind variable names are totally up to you – use something developer *friendly*.

Let’s call it!

Let’s do it in my REST client, Insomnia.

Neena’s been with us for a loooong time.

Or sure, with cURL…

Method Two: PL/SQL REFCURSORs

We’re going to get lots of rows, and throw them into a REFCURSOR. And then we’re going to assign that to a :bind of parameter type ‘RESULTSET.’

One of many ways to do this.

And now let’s call it…

Our JSON doc has an array called “the_employees” – not an accident!

The JSON array is named “the_employees” – that’s because the parameter name is used to create the response-.

Method Three: PL/SQL spit out the JSON directly

We’re going to:

declare a CLOB
generate some JSON into that CLOB
PRINT that CLOB

DECLARE output clob;
BEGIN
    SELECT json_object('id'          VALUE employee_id,
                       'name'        VALUE first_name || ' ' || last_name,
                       'hireDate'    VALUE hire_date,
                       'pay'         VALUE salary,
                       'contactInfo' VALUE json_object('mail'  VALUE email,
                                                       'phone' VALUE phone_number)
            FORMAT JSON) INTO output
  FROM hrrest.employees
  WHERE employee_id = :id;
  htp.p(output);
END;