When building your RESTful (Web) Services in ORDS, you assign a ‘Source Type’ for your handlers.
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 –
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.
Query
Kind of like a collection, but ‘dumbed down.’ It still has paging, but we don’t include any metadata.
For example:
Query One Row
Single record only, no paging logic, no metadata.
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.
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.
5 Comments
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.
I’ll file an ER and talked to the developer. We’ll see what we can do for a upcoming release.
Hello,
Does the POST Handler as well have all the Source Types defined above?
No, your only Source Type for POST handlers is PL/SQL.
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!