ORDS: Empty JSON or 404 for No Records?

It’s all in the P_SOURCE_TYPE!

I got an internal question this morning:

We have a service that is returning an empty JSON response vs a 404 when someone tries to pull up a record that does not exist.

It took me about 30 seconds to remember what might be going on. And I JUST talked about this concept a few weeks ago – ORDS AND SOURCE TYPES.

When you create your GET Handler, most folks will go with the default SOURCE TYPE of ‘Collection Query’ or ‘json/collection’. Using the default might give you something that ‘works,’ but not something that works well for every scenario.

If I use a Collection Query for just a single item in my collection, say an EMPLOYEE that belongs to EMPLOYEES/ – then the SOURCE TYPE has a few big ramifications.

ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps',
      p_pattern        => 'employee/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'emps',
      p_pattern        => 'employee/:id',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select * from employees
where employee_id = :id'
      );

Setting it to ‘json/collection’ means that if our query returns Zero rows, our response will look like this:

{
"items": [],
"hasMore": false,
"limit": 0,
"offset": 0,
"count": 0,
"links": [
{
"rel": "self",
"href": "http://localhost:8080/ords/hr/emp/employee/1"
},
{
"rel": "describedby",
"href": "http://localhost:8080/ords/hr/metadata-catalog/emp/employee/item"
}
]
}

It’s an empty {JSON} document. You’ll also notice that there’s information there for the offset and count…which means we’re taking your source query and wrapping it with a cursor/analytic function to get the paging to work – totally not needed when you’re dealing with a single record vs 25, 250, 2500, or more.

We don’t want this. We don’t want a collection at all, we want a collection item. And if that item doesn’t exist, we want a 404.

To achieve this only required a single line to be changed in our definition of the service:

p_source_type    => 'json/item',

In summary, pay attention to your SOURCE TYPEs!