Generating Links in your Oracle REST API HTTPS Responses

Updated 8 June 2022

---

“What needs to be done to make the REST architectural style clear on the notion that hypertext is a constraint? In other words, if the engine of application state (and hence the API) is not being driven by hypertext, then it cannot be RESTful and cannot be a REST API. Period.

— Roy T. Fielding, Internet God

---

Hypermedia as the Engine of Application State (HATEOAS), basically means that REST APIs primarily communicate via Hypermedia. And what is ‘hypermedia?’ It’s any chunk of content that contains links to additional content such as text, images, video, PDFs, etc.

So when building REST APIs with ORDS, a pretty fundamental concept is generating links.

How can we include a link from a SQL or PL/SQL call?

Quite simply it turns out. Our DOCS covers various scenarios for SQL based REST endpoints.

TL/DR;

If we see a column that starts with $ – ORDS will generate a link for that JSON attribute.

And then, we take the value of the column and append it to the end of the current URI.

Let’s look at the most basic type of example.

The string following the “$” becomes the name of the hypermedia link label, followed by the link itself.

A practical example

Maybe I want to see a list of something, so I write a SELECT * FROM something. But, I also want to include a link to each item as part of the results.

Module: /examples/
URI Pattern: uri/
Code behind the GET:

 select employee_id "$uri", rn, employee_id, first_name, Job_id, hire_date, manager_id, salary, department_id
  from (
          select employees.*,
                 row_number() over (order by employee_id) rn
            from employees
       ) tmp

So we’re querying the EMPLOYEE ID and aliasing it with “$uri”…and then includes the other elements of my employees.

Now let’s open that in my browser.

If I click on one of the links, e.g. “http://localhost:8888/ords/peeps/examples/uri/101” I get a 404.

/peeps/examples/uri/ — exists.
/peeps/examples/uri/:empid — does not, so there’s no template/handler available to handle the link we just generated.

If we build that, it could look like this…

Module: /examples/
URI Pattern: uri/:empid
Code behind the GET:

select employee_id, first_name, Job_id, hire_date, manager_id, salary, department_id
from employees
where employee_id = :empid

So let’s publish our new URI Template and Handler, and then try to click on our link again.

$Whatever

You can call these anything you want. But, your code will need to know what to do with it.

PL/SQL: How to generate/return links via POST or PUT?

So the “$” trick remains, it’s just now you are going to apply it to your HANDLER Parameter name(s).

So if I run this, I’m expecting a text attribute called “message”, and then I should also have a related link called “SQLVersion”

Let’s try it!

Following that link would take me ‘back’ to the previous example.

The Code

Here’s the PL/SQL definition of these two examples.

The SQL w/$whatever and the PL/SQL with SQLVersion links:

-- Generated by ORDS REST Data Services 22.1.1.r1331148
-- Schema: HR  Date: Wed Jun 08 12:15:18 2022 
--

BEGIN
    
  ORDS.DEFINE_MODULE(
      p_module_name    => '101',
      p_base_path      => '/101/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'most basic examples');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'links/PLSQL',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'links/PLSQL',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'BEGIN
 :string  := ''../links/SQL'';
 :text := ''An OUT PARAMETER named $SQLVersion was used to generate this link'';
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'links/PLSQL',
      p_method             => 'POST',
      p_name               => '$SQLVersion',
      p_bind_variable_name => 'string',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => '101',
      p_pattern            => 'links/PLSQL',
      p_method             => 'POST',
      p_name               => 'message',
      p_bind_variable_name => 'text',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => '101',
      p_pattern        => 'links/SQL',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'links/SQL',
      p_method         => 'GET',
      p_source_type    => 'json/item',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'select ''hello world'' hello,
       1 "$whatever"
  from dual');

  
COMMIT;

END;