How to implement pagination with Oracle DB in a readability way

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.