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

SELECT * FROM EMPLOYEES

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

Query

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.

Feed

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.

PL/SQL

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
thatjeffsmith
Author

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.

5 Comments

  1. Hi Jeff

    One of the biggest shortfalls of ORDS collection query / collection query item etc. is the total inability to override Oracle’s lowercase JSON key names. Most JSON key names in the wild are camelCase, so straight away you’re stuffed unless you roll your own – which completely defeats the point . Most of the Devs I work with can’t quite believe Oracle did this, and it makes little sense to us. Oracle should’ve honoured double quoted column name aliasing and defaulted to lowercase when not or at least have an option to honour it…

    select 1 as “myNumber”, 2 as OTHERNUMBER from dual.

    Should yield…

    {
    “myNumber”: 1,
    “othernumber”: 2,
    “links”: [
    {
    “rel”: “collection”,
    “href”: “http://192.168.01.104:8080/services/dev/blah/test/”
    }
    ]
    }

    For many of us it’s the biggest flaw in ORDs and a massive oversight.

    • thatjeffsmith

      I’ll file an ER and talked to the developer. We’ll see what we can do for a upcoming release.

  2. Hello,

    Does the POST Handler as well have all the Source Types defined above?

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