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 Master Product Manager at Oracle for Oracle SQL Developer. 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

    • thatjeffsmith

      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””

Reply To Marcel van Lare Cancel Reply