If you want to fetch using Cursor in PL/SQL, you can use BULK COLLECT with LIMIT
SQL> set pagesize 8 SQL> set pause on
Or if you want pagination you can refer AskTom
SQL> set serverout on SQL> declare 2 cursor c_emp is select * from emp ;--order by will be required 3 type t_emp is table of c_emp%rowtype; 4 t_emp1 t_emp; 5 begin 6 open c_emp; 7 loop 8 fetch c_emp bulk collect into t_emp1 limit 5; 9 dbms_output.put_line(t_emp1.count||' Records Fetched'); 10 exit when c_emp%notfound; 11 end loop; 12 close c_emp; 13 end; 14 / 5 Records Fetched 5 Records Fetched 4 Records Fetched PL/SQL procedure successfully completed.
select * from ( select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from ( your_query_goes_here, with order by ) a where ROWNUM <= :MAX_ROW_TO_FETCH ) where rnum >= :MIN_ROW_TO_FETCH;