>I need to know is there any way in which i can call only a specific no. of
>records in a ref cursor.
>I need to get a large amount of records using a Stored Procedure but i
>display only 10 records at a time. I give pagination for browsing through
>the records. But what this does is i have to call the Stored Procedure again
>and again for every page which loads the entire amount of records resulting
>in wastage of memory.
>What i would like is some way in which i can retrieve only ten records using
>the Stored Proc. and then if i require page 5 then i call the SP to give me
>Can this be done, if possible, only on the GUI side or will the Sp's have to
>be modified too.
>the GUI used is Java, JSP
You need to tell the stored procedure which page you want, and how big each page is, and then modify the query like this:
create or replace procedure myproc
(p_page_no in number, p_page_size in number, p_refcur out sys_refcursor)
open p_refcur for
select ename, sal from
( select ename, sal, rownum rn from
( select ename, sal from emp
order by ename
where rownum <= p_page_no*p_page_size
where rn > (p_page_no-1)*p_page_size;