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.

This is much user user friendly.

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

The default is 25.

Now, this service is powered by a SQL statement.

Boring, I know.
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?

NO.

We use the power of analytic functions!

We add a little spice to your SQL recipe.

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

ROW_NUMBER (Docs)

This makes paging the query results very easy.

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values 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.

Oracle Docs

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…
The dreaded 500 🙁

Note I have DEBUG and PRINT TO SCREEN enabled for my local ORDS, so I get to see the stack trace on my 500 screen.

The solution? Disable paging…

Woohoo.

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.

Here’s one example.

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.

3 Comments

  1. Hi Jeff,

    Thanks for this information. It is of great help to me as I am in a project where I need to implement pagination for a couple of rest services. All of them are built on top of a pretty generic pl/sql process handling e.g. parameter validation, output format, ….

    Would you also happen to know how ORDS “extracts” the typical information at the bottom of a “paginated” response? I am especially interested in the hasmore-element as this is the basis for e.g. the next relation link. I assume ORDS somehow gets the total count and compares this to the offset + limit. But does this mean ORDS fires the same query twice, a first time for the results (as explained in your article) and a second time for the count, or is he able to do this in one run?

    Thanks for your input.

    Rgds,

    Mike

    • thatjeffsmith

      No, if paging is set to 25, we get 26 or 27…if they’re there, we know there’s a next page.

      We never do a discount, as that’s sometimes just as expensive as running the query.

Write A Comment