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 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:
As well as I consider this a clean way to have a paginated recordset, I found it also very performing.
Top comments (1)
How do you think about new function of Oracle DB: OFFSET...FETCH...?