Oracle stored procedure to return paged resultset

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.

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.