DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Newsletter
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Related

  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations
  • Building an Enterprise CDC Solution
  • 5 Key Postgres Advantages Over MySQL

Trending

  • Why Your CI Pipeline Is Flaky and How to Make It Reliable
  • Data Integration Landscape 2026: Event Streaming, API, and Batch in the Era of Agentic AI
  • The Self-Hosting Math Changed While We Were Still Arguing About Sovereignty
  • Build a Voice Assistant With Gemini Live API

Draft This article hasn't been published, so readers can't see it.

In moderation This article is waiting for review, so readers can't see it yet.

Deleted This article has been deleted, so readers can't see it.

  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Do Pagination in Oracle: SQL Query With Example

How to Do Pagination in Oracle: SQL Query With Example

Let's take a look at how to implement pagination when using an Oracle database, along with a quick example.

By 
Javin Paul
·
Aug. 01, 18 · Tutorial
Post on X (opens in a new tab) Share on LinkedIn (opens in a new tab)
85.4K Views

Join the DZone community Save articles, join the discussion, and download Refcards and Trend Reports with a free account.

Join for free Already a member? Log in

Many times, we need a SQL query that returns data page by page i.e. 30 or 40 records at a time, which can be specified as page size. In fact, Database pagination is a common requirement of Java web developers, especially dealing with the largest datasets. In this article, we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also uses display tag for paging in JSP which supports both internal and external paging. In case of internal paging, all data is loaded into memory in one shot and display tag handles pagination based upon page size but it only suitable for small data where you can afford those many objects in memory.

If you have hundreds of row to display than its best to use external pagination by asking the database to do pagination. In pagination, ordering is another important aspect which can not be missed.

It's virtually impossible to sort large collection in Java using limited memory available to Java program, sorting data in a database using ORDER BY clause itself is a good solution while doing paging in a web application.

Pagination SQL Query in Oracle 10g Database With Example:

In database paging, we only query data that we are required to show or maybe up to 3 pages just to prefetch some data in advance for performance reason.

Thankfully, Oracle database provides a convenient method row_number() that can be used to provide a unique row number to each row in result set. See Oracle PL/SQL Fundamentals — Part 1 to learn more about row_number and other pagination methods in Oracle database.

By including row_number() in the query, you can produce a result set that is numbered, and then its just a job to retrieve data from specified indexes or pages. Here is an example of a pagination query in Oracle 12c database:

SELECT * FROM (
    SELECT
      ord.*,
      row_number() over (ORDER BY ord.order_id ASC) line_number
    FROM Orders ord

  ) WHERE line_number BETWEEN 0 AND 5  ORDER BY line_number;

This will print the result of a query including an additional column called line_number, which will automatically be populated by Oracle because of row_number() function.

You can also see Oracle Database 12c Fundamentals to learn more about the row_number function of Oracle 12c database, which is a free online course from Pluralsight to learn Oracle database in detail. There are two parts of this tutorial, both are freely available once you signup for the 10-day free trial on the Pluralsight website.

By using this line_number column, now we can get the result page by page based on the size of the page or more specifically from one row to another like from 1 to 30 or 5th to 30, since you can pass starting row and end row from Java program to Oracle database.

Though I know MySQL database has inbuilt paging support using the LIMIT keyword, this row_number() function of Oracle is equally useful for paging in Oracle database.

I have also found that SQL Server also supports row_number() function, which can be used to get data page by page in SQL Server. By the way, if you are preparing for Oracle Certification e.g. 1Z0-066 to become a certified Oracle Database administrator, then you can check out David Mayer's free 1Z0-066 dumps for reference.

Database sql Data (computing) MySQL

Published at DZone with permission of Javin Paul. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations
  • Building an Enterprise CDC Solution
  • 5 Key Postgres Advantages Over MySQL

Partner Resources

×

Comments

Loading comments

The comments didn't load

Please try again in a moment.

Likes

Loading

The next likes didn't load. Please try again.

The likes didn't load

Please try again in a moment.

See who liked this

Log in or join DZone for free to see who liked this article.

Log in Join for free
  • RSS
  • X
  • Facebook

ABOUT US

  • About DZone
  • Support and feedback
  • Community research

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 215
  • Nashville, TN 37211
  • [email protected]

Let's be friends:

  • RSS
  • X
  • Facebook