Oracle stored procedure to return paged resultset

Question

Is there a way for an Oracle stored procedure to return paged resultset (as refcursor) back? For example, I would like to pass to the stored procedure the number of records to return and the page number interested. Then I only want to see those number of records back to my client. How is that done on the Oracle side?

i.e.

var v_numrecords number := 30;
var v_pagenum number: = 5;
var v_rows refcursor;
exec my_stored_proc(:v_rows, :v_numrecords, :v_pagenum);
print v_rows;  -- expecting only 30 records of page number 5 back

thanks much.

Answer 1

You can use ROWNUM. You need to apply that after the results have been sorted.

Here's an example: we want rows 6 through 10.

   SELECT *
    FROM (SELECT e.*, ROWNUM rnum
          FROM (SELECT *
                FROM employee
                ORDER BY employee_id) e
          WHERE ROWNUM <= 10)
    WHERE rnum >= 6;

To get records 121 through 150, you should replace the 10 in the inner SELECT with 150, and then replace the 6 in the WHERE clause with 121.

In your stored procedure, you will want to replace these hard-coded values with variables.

Answer 2

If you want Oracle to return rows N through M of a result set, you've got a few choices, depending on what you really want.

If you want the entire result set to be static - the rows on page 5, for example, will never possibly appear on any other page, you'll have to either:

1. Open a cursor for the entire result set, and perform pagination in the application.

2. Have the the stored procedure persist the entire result set into a global temporary table and paginate off of that. That makes your stored procedure more complicated, as it will have to build the result set from scratch when the application tells it to.

If you don't care that the rows on page 6 might have been on page 5 (the result set is dynamic), then have the cursor perform the customary Oracle pagination:

select [columns] 
  from (select [columns], rownum as rn
          from [some such tables]
         where [some such conditions]
           and rownum < (:v_pagenum + 1) * :v_numrecs
         order by [some expression])
 where rn >= :v_pagenum * :v_numrecs;