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.