As I’ve been getting up to speed with ORDS REST services over the last couple weeks, Tim Hall’s Oracle-Base tutorial about REST services basics proved very helpful. I started by experimenting with ORDS’ support for automatic handling of different built-in datatypes to and from JSON. In many cases, this lets you focus on your core PL/SQL logic without having to work directly with JSON.
However, to make sure I was understanding all the moving parts, I set myself the task of creating a service that could process an arbitrary JSON document as the request and return back an arbitrary JSON document in response (possibly created programmatically or by doing a JSON SQL query or some combination of the two). After reading another of Tim’s articles RESTful Web Services Handling Complex JSON Payloads, I set out to try my own example. The Oracle reference docs on PL/SQL JSON datatypes also came in handy while doing this.
In my head, what made the most sense to represent a PL/SQL procedure that accepted a JSON request and returned a JSON response was a procedure specification like this:
procedure generic_service(p_request in json_object_t,
p_response out json_object_t)
The simplest version of such a service would be an “echo” service that just returned the JSON document passed in as the response:
procedure generic_service(p_request in json_object_t,
p_response out json_object_t) is
begin
-- Start by just echoing the request as the response
p_response := p_request;
end;
I knew that once I got this “echo” service working I could modify the internals of the generic_service() procedure to do whatever I needed to do to process the incoming JSON and return a custom output JSON.
Let’s assume the JSON payload we need to process looks like this:
{
"customer_id" : 1234,
"items" : [
{
"product_number":"978-0262542456",
"quantity":1
},
{
"product_number":"978-0358446286",
"quantity":1
}
]
}
In an EXAMPLES schema I’d previously enabled for ORDS access, I ran the following PL/SQL block to define the module, template, and two handlers (one for the POST verb and another for the PATCH verb). Notice that I’m passing the BLOB-valued :body bind variable to the json_object_t() constructor in order to pass the resulting JSON object as the value of the p_request parameter in the call to generic_service(). This results in parsing the incoming JSON payload into a form that’s easy to work with in PL/SQL.
begin
ords.define_module(
p_module_name => 'rest-jsonecho',
p_base_path => 'rest/json/',
p_items_per_page => 0);
ords.define_template(
p_module_name => 'rest-jsonecho',
p_pattern => 'echo/');
ords.define_handler(
p_module_name => 'rest-jsonecho',
p_pattern => 'echo/',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => q'[
declare
l_response json_object_t;
begin
generic_service(p_request => json_object_t(:body),
p_response => l_response);
owa_util.mime_header ('application/json', true);
-- Show example of modifying the response from the invoked
-- json-in-json-out method before returning.
l_response.put('method-used-was','post');
htp.p(l_response.stringify);
END;
]',
p_items_per_page => 0);
ords.define_handler(
p_module_name => 'rest-jsonecho',
p_pattern => 'echo/',
p_method => 'PATCH',
p_source_type => ords.source_type_plsql,
p_source => q'[
declare
l_response json_object_t;
begin
generic_service(p_request => json_object_t(:body),
p_response => l_response);
owa_util.mime_header ('application/json', true);
-- Show example of modifying the response from the invoked
-- json-in-json-out method before returning.
l_response.put('method-used-was','patch');
htp.p(l_response.stringify);
END;
]',
p_items_per_page => 0);
commit;
end;
When the procedure call to generic_service() returns, the l_response variable contains the JSON object returned in the p_response out parameter. The handler’s PL/SQL code can then perform any post-processing if necessary. In the two example handlers above, notice that I’m simply setting one additional JSON property named method-used-was so you can see a difference between sending the sample payload using the POST or PATCH verb.
In order to write out the JSON response, we start by using owa_util.mime_header() to add the appropriate HTTP response header that signals to the caller that the response is a JSON document. Then we use the venerable htp.p() procedure to write the “stringified” version of the JSON object out to the response. The json_object_t object’s stringify() function prints out the contents of the JSON data structure using the right combination of curly braces, colons, quotes, and brackets as needed to produce the expected text representation of the data.
To test out the result, I sent a POST and a PATCH request to my local ORDS server with the URL http://localhost:8181/ords/examples/rest/json/echo/
After my first attempts were failing to work, I learned from colleagues that ORDS is very exacting when matching the URL for the defined template handlers. My PL/SQL that defined the template handlers used "rest/json/" for the module base path and used "echo/" for the template pattern, so sending my request to the URL http://localhost:8181/ords/examples/rest/json/echo(without the trailing slash) was not matching any defined template handler. Good lesson learned!
Here’s what my Postman window looked like when testing the POST request:
With this basic skeleton in place, I aim to create a number of custom JSON-in, JSON-out services for a sample app I’m working on that will follow this pattern. If you notice something I could be doing even better, please let me know on Twitter. I’m always happy to learn ways to improve my code, and of course still consider myself an ORDS newbie, so I’m sure there is plenty more to learn.
Dive Into APEX 