ORDS service based on a SQL query, join or no join, shouldn't be a problem.
We automatically page the results, so size of result-set shouldn't be a problem.
So do I need to pull the full data set across from SAAS via REST and then join that to the local custom tables or is there some other way to do this? All the examples of REST I have seen are just to call a GET request to retrieve data but nothing that is close to what I need to do.
you want one call to get data from 2 places?
if the data is sitting in different databases, and aren't available to each other via DB_LINKs, then at some point you would need to join the data together locally, yes?
ORDS does also have a REST SQL feature where you could create a pl/sql package to make a HTP call to get data and create VIEWS on the responses of those queries, Dermot talks about that here
Many thanks, this looks to be what I need.. I will take a look.