Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Pagination in ref cursors

    >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
    >records 41-50..
    >
    >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
    >Please help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to tell the stored procedure which page you want, and how big each page is, and then modify the query like this:
    Code:
    create or replace procedure myproc 
    (p_page_no in number, p_page_size in number, p_refcur out sys_refcursor)
    is
    begin
      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;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •