Pagination of oracle result set, returned by stored procedures

Question

Hi all!

In our project, we have all DB manipulations concentrated in Oracle stored procedures. Result sets is returned by OUT parameter of SYS_REFCURSOR type. Now we have a task to make pagination, and we don't want to rewrite all of our procedures, adding 2 parameters (pageNumber and pageSize) to them and editing WHERE clauses. We have an idea to write some type of wrapper procedure, that can call each of stored procedures, get slice from returned result set and pass it out. The problem is that all procedures get different parameter list, so we don't know how to implement it in wrapper.

Can somebody help us? Maybe we can solve it using Java stored procedures? Maybe, we can solve it in some different way, not using wrapper?

Sorry about my English.

Thx.

Answer 1

It's good that you used stored procedures to perform the selects, but unfortunate that you didn't have all the requirements up front - an agile project I'd guess ;-) I really think the only viable way forward is to modify the stored procedures. How many are there? If it is very many then perhaps there could be a case for writing a program to automate the task - i.e. to read the procedure source, add the parameters and extra WHERE clause code.

Answer 2

How are you calling your stored procedures? Are you printing the results directly from the calling program? It sounds like you might want to look into a reporting tool that can handle the printing.

Answer 3

This might work, but it's

• untested,
• possibly impractical, as it relies on the stored procedure called being a string,
• possibly poor performing, as it
  • uses dynamic SQL and
  • reruns the base SQL to paginate; and
• untested.

create or replace function paginate_sys_refcursor
  (p_page_number   in number,
   p_rows_per_page in number,
   p_sp_call       in varchar2(4000))
return sys_refcursor
authid current_user
as
  open p_sysref_cursor for
    'select *  ' || 
    '  from (select c.*, rownum as rn ' ||
    '          from cursor(' || p_sp_call || ')' ||
    '         where rownum = ' || (p_page_number - 1) * rows_per_page ;
    ' where rn >= ' || (p_page_number - 1) * rows_per_page ;

  return p_sysref_cursor;
end paginate_sys_refcursor;