ORDS – HTTP Responses and Forwarding for your REST APIs

The forwarding bit isn’t a new feature, but we have introduced new ORDS response header variables in version 18.3 of ORDS.
You can now also read in the body of your request as a CLOB vs a BLOB.

This post shows how to take advantage of both features.

The only thing ‘new’ here is the name of this ORDS parameter. From the early days of the Oracle Application Express Listener, we always had X-APEX-STATUS-CODE and X-APEX-FORWARD.

Now that we’re ORDS, and this feature isn’t directly tied to APEX to begin with, we’ve deprecated these terms for ORDS 18.3, and have introduced two new ones:

• X-ORDS-STATUS-CODE
• X-ORDS-FORWARD

Deprecated: This means we’re giving notice – eventually the X-APEX- codes won’t work anymore. When building new handlers, use X-ORDS- and plan on updating your existing code to switch over.

Everything still works as before.

But, how does this actually work? The Docs explains it thusly:

“When the Oracle Application Express Listener sees this header in the response, it abandons generating a JSON representation of the outbound parameters and attempts to return a representation of the indicated location.”

I’ve bolded the important parts.

Here’s how this looks in SQL Developer when implementing your Handler:

So how does this look in practice? Have no fear, I’m going to share the ENTIRE module definition below. But for now, let’s look at this from a high level.

Part One: POST a Text File to a CLOB

My POST handler is going to read in the contents of the request body as a CLOB. This is a new feature in 18.3 – you can now use :body_text (CLOB) vs :body (BLOB) – you may find this handy when working with JSON documents, or just whenever you want to deal with CLOBs vs BLOBs.

My Handler block is pretty simple, but basically, whenever someone POSTs up a TEXT file to /ords/hr/lobs/clobs/ this gets executed:

declare
 next_id integer;
begin
 select (max(id)+1) into next_id from clobs;
 insert into clobs (id, texts) values (next_id, :body_text);
 commit;
  :forward_location := './' || next_id;
  :status := 201;
end;

My code sucks, I don’t have a sequence or IDENTITY column, I’m doing very poor practice on the NEXT_ID bits – so ignore that. The only interest parts here are the :body_text and :forward_location.

The forward_location bind variable is tied to X-ORDS-FORWARD. When ORDS sees that come back in the RESPONSE HEADER, it’s going to go to that location, and retrieve that document. It just so happens that document is served up by another GET handler, but we’ll see that bit in a moment.

Note I also have :status tied to X-ORDS-STATUS-CODE, and set to 201. This is being NICE. In HTTP land, it means we’ve created a new item in our /clobs/ collection. And instead of showing a link to go get it, we’re just going to actually go GET it, hence the ‘FORWARD’ terminology.

Look, when I do the POST, I don’t get an ORDS {JSON} 200 response. I get a {201} AND it just prints the document. Instead of asking my user if they want to get it with a click, we want the response to be the document itself.

My response is the contents of the file I just uploaded. If you want to see where that’s coming from, we can just peak into response header:

Part Two: GET the Text File

This one is VERY boring. The only weird bits are I have it setup as a GET Media Resource, and I’m just reading the first 500 characters from the CLOB.

The Handler code:

select 'text/plain', substr(texts, 1, 500)
from clobs
where id = :id

How I Wasted 2 Hours of My and my Co-Worker’s Time

Let’s go back and look at my forward location I’ve dynamically set in my POST handler. This line of code:

:forward_location := './' || next_id;

Pretty simple, right? I’m just setting a relative PATH to my new document. But, I screwed up. When I had originally wrote this Module, I had my resource URI set to /clob. What it SHOULD HAVE BEEN WAS /clobs/.

Oops.

My esteemed colleague enjoyed very much reminding me I had just chastised a customer the other day on this same subject. He talks about the importance of those trailing slashes here.

Maybe now this will be SEARED into my mind now.

The Code

Remember, you promise not to blindly copy and paste this, and I promise to made code that is simple enough to inspire you to go and build great, awesome things.

-- Generated by Oracle SQL Developer REST Data Services 18.3.0.276.0148
-- Exported REST Definitions from ORDS Schema Version 18.2.0.r1831332
-- Schema: HR   Date: Mon Oct 08 12:19:41 EDT 2018
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);    

  ORDS.DEFINE_MODULE(
      p_module_name    => 'lobs',
      p_base_path      => '/lobs/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'lobs',
      p_pattern        => 'clobs/',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'lobs',
      p_pattern        => 'clobs/',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'text/plain',
      p_comments       => NULL,
      p_source         => 
'declare
 next_id integer;
begin
 select (max(id)+1) into next_id from clobs;
 insert into clobs (id, texts) values (next_id, :body_text);
 commit;
  :forward_location := ''./'' || next_id;
  :status := 201;

end;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'lobs',
      p_pattern            => 'clobs/',
      p_method             => 'POST',
      p_name               => 'X-ORDS-FORWARD',
      p_bind_variable_name => 'forward_location',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'lobs',
      p_pattern            => 'clobs/',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => NULL);      
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'lobs',
      p_pattern        => 'clobs/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select id, substr(texts, 1, 25),
 ''../clobs/'' || id as "$full_text"
from clobs'
      );
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'lobs',
      p_pattern        => 'clobs/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'lobs',
      p_pattern        => 'clobs/:id',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page =>  25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select ''text/plain'', substr(texts, 1, 500)
from clobs
where id = :id'
      );


  COMMIT; 
END;