How To Fetch X Records At A Time as Pagination SQL Query From Oracle 12 C DB

How To Fetch X Records At A Time as Pagination SQL Query From Oracle 12 C DB

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s