When building your RESTful (Web) Services in ORDS, you assign a ‘Source Type’ for your handlers.

What you pick here is important, but when in doubt, probably go with Collection Query.

The selection you make here has a big impact on how ORDS formats the response.

In general, you give us some SQL or PL/SQL. We execute it, get the response, and turn it into {JSON}. It’s a bit more complicated than that though, so I wanted to walk through ALL of the different Source Types.

Collection Query

We expect multiple items in the collection (rows from your query for example), and we’ll page the response. For example, if you build a service around


We don’t return all 160,000 employees for your GET request. No, we use an analytic query to ‘window’ the records into pages of 25 rows at a time.

What you see in the response is something like this –

The paging size/offset is defined in ORDS, the module, and the service itself.

Collection Query Item

For a single record, there’s no paging – so no fancy analytic query windowing stuff on the back end. And, if your query actually returns more than 1 record…we don’t care. We’re just going to return the first no matter what.

We assume your template layout allows for this to BELONG to an actual collection, so you get the collection link, which just strips out the last bit of the URI from the ITEM


Kind of like a collection, but ‘dumbed down.’ It still has paging, but we don’t include any metadata.

For example:

There’s no collection metadata…the HasMore, Limit, Offset, Count attributes are all gone.

Query One Row

Single record only, no paging logic, no metadata.

Hopefully self-explanatory.


This one is kind of cool. It assumes that the first column in your result set will be an ID, which can be used to create a link. So you don’t need to do the ‘$column as alias trick’

So, make sure your templates have a pattern where there’s a FEED (which has a ID in the first column), which can then direct your API consumers to another end point to get the item.

If you build the Feed service, but not the service for the items in the feed, this might not be very useful to you.


We’ve talked about this before. You can run code, print things via OWA, bring data back via the response header or body using defined parameters.

Media Resource

This one is fun. We don’t do the magic-JSON transform of your query results. We look at the first column of your 1-record response, and we set that as the Mime Type, and then we take the raw, untouched value of the second column as the content.

So if you want to display a PDF, or a PNG/JPEG/GIF, or if you want to return your own {JSON} – this is the way to go.

media resource source type with content type set to image/png

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 Comment

  1. Hi Jeff,

    I am trying to create an ords service that results in a .csv file. I am having trouble with chrome not recognizing it as .csv and the file is comming without the headers. Could you give some light about this problem. I think that maybe something simple but I just can’t figure it out.

    Thank you!

Write A Comment