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.

7 Comments

  1. Hi Jeff,

    Is there a way to get the last page with ORDS (or the total count of the query WITH filters applied) ?
    ORDS gives only the “next”, and sometimes there i a need to navigate through the list with page’s numbers.

    I tried to insert the count in the query, but filters are applied after, meaning the count corresponds to data without filters.

    Regards,

    • thatjeffsmith

      We never do a rowcount. We only grab a specific range of rows, never knowing if it’s the last page until we’ve grabbed it and see there are no rows coming ‘next.’ Turn off paging if you want to do something like append the rowcount. Or use a stored procedure, but you’ll have to write your own paging scheme.

    • Stephane

      Thanks for the answer Jeff

      I took the stored procedure route, because i need to apply my filters before the count, and ORDS doesn’t seem to be capable to do it in that order.

      Is there a way to submit something to the team developing ORDS, to be able to get this kind of information if needed ?
      It seems to me that this is a pretty basic information for pagination to have the total count to display the final page at the bottom of a table for example.

    • thatjeffsmith

      Even when running queries in oracle, there’s no way to get the row count. When you ask for a row count in SQL Developer for a query, we actually run another query to find out.

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