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.

      p_module_name    => 'emps',
      p_pattern        => 'employee/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
      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.

This is what we want the response to be in this scenario.

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!


I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.


  1. Marcel van Lare Reply

    Hi Jeff,
    I have a related question: Perhaps asked a lot before…

    Is it actually a “json standard” or an Oracle ORDS implementation (standard) that a GETALL collection Query returns which gets no results, that is returns a status 200 OK with a empty collection respons like { “items”: [],….}?
    Why doesn’t is return 404 NOT FOUND with no respons, like a specific get would?

    Regards, Marcel

    • I think it’s our standard, say you ask for employees…it’s not that the collection doesn’t exist, it’s that it’s empty..poking around in our internal doc I see..

      “OracleRESTStandard says “If the collection is empty, then it must return an empty collection””

Write A Comment