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.

Now what???

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…

I swear, it’s real!

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:

  1. declare a CLOB
  2. generate some JSON into that CLOB
  3. 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;

Now let’s call the API. No one ever talks about poor Shelli, so let’s pull up EMPLOYEE number 116.

You’re in charge, you’re generating the response via your SQL vs ORDS taking the results or output and doing it for you, so if you want CamelCase JSON attributes, you do you.

I’m using the JSON_OBJECT function call to generate JSON from a SQL query result, but there’s a TON of native JSON support in Oracle Database (Docs.)

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment