Updated 31 March 2022
REST APIs can be useful, and even fun to build.
Imagine an interface that would allow you to get a list of all the items in your collection.
Now that’s awfully vague, so let’s use our imagination a bit.
- all of the employees in our company
- all of the beers in my Untappd diary
- all of the objects in our database
- all of the songs in our iTunes library
Hopefully for the scenario YOU have imagined, it’s a ‘big’ collection. Big enough, that you wouldn’t want to get everything, all in one go.
Imagine accessing this API from your mobile phone, on a fast moving train, hopping cell towers or god forbid using the free WiFi, and having to wait for the response payload of like 150,000 employees?
That would NOT be fun.
So a common pattern is to instead PAGE the results. You get the first X records, with links to traverse the collection.
Are there MORE records? If so, provide a link.
So how does ORDS make this happen?
When I define my GET handler, I’m giving it a SQL statement. And I’m also saying, hey, I want this to be a collection, AND I want my pagesize to be X (in this case, 25).
Now, this service is powered by a SQL statement.
SELECT * FROM ALL_OBJECTS
Wait, that’s going to bring back a TON of stuff. How is ORDS going to page the results?
Do we…like do a fetch, hold onto the process/query, and do more fetches later if necessary?
Do we…get ALL the data on the mid tier, cache it or something, and dole it out as necessary?
We use the power of analytic functions!
This is what we actually run.
SELECT * FROM ( SELECT Q_.*, ROW_NUMBER() OVER( ORDER BY 1 ) RN___ FROM ( SELECT * FROM ALL_OBJECTS ) Q_ ) WHERE RN___ BETWEEN :1 AND :2
This makes paging the query results very easy.
By nesting a subquery usingOracle Docs
ROW_NUMBERinside a query that retrieves the
ROW_NUMBERvalues for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting.
What we do, if your pagesize is 25, we actually try to get 26 records. If that 26th row is there, we return the 25 records, and then we KNOW there is at least one more page. If there’s NOT that 25th row, then we know we’re done, and there’s no link for the next page.
So what does this mean, for me?
- You provide the base query, ORDS handles the paging.
- EACH request you make, results in the query being executed, again.
- The bigger the pagesize, the more resources you’re going to consume.
- You have to mind your CURSOR expressions, else…
Note I have DEBUG and PRINT TO SCREEN enabled for my local ORDS, so I get to see the stack trace on my 5xx screen.
The solution? Disable paging…
Want a complete demo of building a CURSOR based SQL GET Handler? I have the code and steps here on StackOverflow.
What about PL/SQL?
Let’s say you have a stored procedure back ending your REST API, and said PL/SQL returns a LOT of data via a REF Cursor. I want to page that!
Well, you get to write some more code, as ORDS does not provide a mechanism to auto-page your REFCURSORs.
Thankfully, there are many folks in the community that have shared how to do this.