How to implement pagination with Oracle DB in a readability way

#showdev #oracledb #node #sql

It was a Sunday morning. An early Sunday morning. I was waiting for my home wake up. So I looked again to my uncompleted task: how to paginate my recordset with Oracle DB (11c...).

I was inspiring to found a solution without using subselect.

I tried to avoid using subselect because I consider it not so readable (I found a lot of example with it). I've found my way using WITH statement.

The WITH statement permits to have a clean and simple SQL code.

This is the result.

WITH RECORDSET AS (
    -- put here your select with the complete recordset.
    SELECT FIELDA, FIELDB, FIELDC FROM TABLE
), 
NUMBERED AS (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELDA) RN, 
    RECORDSET.*
    FROM RECORDSET)
SELECT
    -- page number parameter
    :page_number PAGE_NUMBER, 
    -- total recordset pages
    CEIL((SELECT COUNT(*) FROM NUMBERED) / :page_size) TOTAL_PAGES, 
    -- page size parameter
    :page_size PAGE_SIZE, 
    -- total rows
    (SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS, 
    NUMBERED.*
FROM NUMBERED
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

This code ask for two parameter: :page_size of your recordset and :page_number you want retrive.

The first fields contain pagination data: PAGE_NUMBER, TOTAL_ROWS, PAGE_SIZE and TOTAL_ROWS.

As well as I consider this a clean way to have a paginated recordset, I found it also very performing.

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →