How To Do Pagination in Oracle

In MySQL, pagination is very easy because of the Limit keyword in the Select statement. But Oracle don't have the Limit keyword as of this writing. So you have to do a little trick using subquery and the pseudo column rownum. Here's a simple tutorial on how to do it.

1. Pagination Formula

This formula is what you will put in your condition using between in the condition section of your Select statement.

Pagination formula, where:
pg = page numner,
lm = limit per page

(pg - 1) * lm to pg * lm;

2. Creating and Populating the Table
We will create the table that we will be using and populate it using PL/SQL.

--CREATE THE TABLE
create table FOO(
  ID   NUMBER(5),
  NAME VARCHAR2(20)
)

--POPULATE THE TABLE
begin
  for i in 1..100 loop
    insert into foo(name) values(to_char(i));
  end loop;
end;

3. Procedure
Now, we will create a procedure to do the querying from the Oracle database. We used the pagination formula in our condition section of our Select statement.

--PAGINATION PROCEDURE
create or replace procedure foo_pagination(
  p_pageno in number,
  p_limit IN NUMBER) IS
  
  --parameterized cursor
  CURSOR c_foo_page(p_pg in number,
                    p_lm IN NUMBER) IS 
  SELECT x.id, x.NAME
  FROM 
    (SELECT id, NAME, rownum AS row_num
    FROM foo) x
  WHERE x.row_num BETWEEN (p_pg - 1) * p_lm AND p_pg * p_lm;
  --variable to hold the rows
  v_foo_rows foo%ROWTYPE;
  
BEGIN
  dbms_output.put_line('Values in Page #' || p_pageno);
  
  --open cursor
  OPEN c_foo_page(p_pageno, p_limit);
  LOOP
    --fetch record from cursor
    FETCH c_foo_page INTO v_foo_rows;
    EXIT WHEN c_foo_page%NOTFOUND;
    dbms_output.put_line('ID: ' || v_foo_rows.id || 
                         ' - Name: ' || v_foo_rows.NAME);
  END LOOP;
  
  --close cursor
  CLOSE c_foo_page;
END foo_pagination;

4. Testing
Below is the sourcecode to test our procedure. We will use PL/SQL to test it.

--TESTING THE PROCEDURE ABOVE
begin
  foo_pagination(2, 10);
end;

That was it. Using subquery and the pseudo column rownum, you can do pagination in Oracle, just like what you can do in MySQL.