This post give an example about How To Fetch X Records At A Time as Pagination SQL Query from Oracle 12 C Database.

Implementing Pagination in Oracle 12c

LOGS_TABLE is like below :

CREATE TABLE LOGS_TABLE
(
  USER_ID      VARCHAR2(100),
  DATE_TIME    VARCHAR2(50),
  RECORD_COUNT VARCHAR2(10),
  RESULT       VARCHAR2(25),
  LOGS         VARCHAR2(250)
)

Select * from LOGS_TABLE ORDER BY DATE_TIME OFFSET (PAGE_NO*RECORD_COUNT) ROWS FETCH NEXT RECORD_COUNT ROWS ONLY;

If PAGE_NO 0 and RECORD_COUNT 10 , then it will fetch fits 10 rows from DB

Select * from LOGS_TABLE ORDER BY DATE_TIME OFFSET (0) ROWS FETCH NEXT 10 ROWS ONLY;

If PAGE_NO 1 and RECORD_COUNT 10 , then it will fetch fits 10 rows from DB

Select * from LOGS_TABLE ORDER BY DATE_TIME OFFSET (10) ROWS FETCH NEXT 10 ROWS ONLY;

and so on..

.

.

If PAGE_NO N and RECORD_COUNT 10 , then it will fetch fits 10 rows from DB

Select * from LOGS_TABLE ORDER BY DATE_TIME OFFSET ((N-1)*10) ROWS FETCH NEXT 10 ROWS ONLY;

• Database Technologies