Updated 8 June 2022


Helped invent HTTP and is responsible for REST

“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 column alias is double-quoted and starts with the “$”

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.

So we have the employee record itself, and the link is listed afterwards, note the link is the existing URI with the employee ID tacked onto the end of it.

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.

Ta-da!

$Whatever

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

Try to use descriptive hypermedia labels, or you’re not being very friendly.

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).

The parameter name determines the attribute label in the HTTP response {json} body.

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!

Just what I wanted 🙂

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;
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.

5 Comments

  1. Andrew McPherson Reply

    Hi,
    What seems to be missing here is how to generate links to other resources. The $ notation provides an easy way to handle the current resource, but one of the great things about HATEOAS is being able to follow the links between resources. Your example includes the metadata catalog link, but I don’t see any way to generate this kind of other resource link.
    I think you’ve done a great job with ORDS so far. Its probably the best thing in the web space since the OWA toolkit.

    • Andrew McPherson

      Hi Jeff,
      Can you provide an example? If I use $../ it just gets used in the name without affecting the path.

    • Andrew McPherson

      Hi Jeff,

      Worked it out, ../ is part of the data not after the $ in the label.

      Still having a problem with load balancing, each ORDS instance uses its own URL, can’t see anyway to use the load balancers URL

    • Still having a problem with load balancing, each ORDS instance uses its own URL, can’t see anyway to use the load balancers URL

      Well that’s a COMPLETELY different subject. Can you start from the beginning, what are you trying to achieve, and where are you running into a wall?

Write A Comment