sql - Oracle procedure pagination bringing back more rows than expected

Question

This is a shortended down procedure for example purposes. The problem i am have is when i go to the next page on my application it brings back 5, then 10, then 15, then 10. I only want to bring back 5 every time. It appears to happen when the date is descending.

procedure GET_DATA( p_sort_col IN VARCHAR2, p_sort_order IN VARCHAR2,
  p_page_index IN NUMBER DEFAULT null,
  p_page_size IN NUMBER DEFAULT null, p_cursor  out l_cursor)
AS
begin
OPEN p_cursor FOR
    select * from (
select* from (select rownum rn, Name, DateCol, ROW_NUMBER() Over( ORDER BY 
 CASE
        WHEN p_sort_col = 'Name' and p_sort_order = 'asc' THEN
         Name
END ASC,
  CASE 
            WHEN p_sort_col = 'DateCol' and p_sort_order = 'asc' THEN
           DateCol
     END ASC,
CASE 
        WHEN p_sort_col = 'Name' and p_sort_order = 'desc' THEN
         Name
     END DESC,
      CASE 
            WHEN p_sort_col = 'DateCol' and p_sort_order = 'desc' THEN
            DateCol
     END DESC) from gdpr_document_manager_audit  
     ) where  rownum < ((p_page_index * p_page_size) + 1 )         
)WHERE rn >= (((p_page_index-1) * p_page_size));
END GET_DATA;

actual query

question from:https://stackoverflow.com/questions/65847365/oracle-procedure-pagination-bringing-back-more-rows-than-expected

Answer 1

I am assuming that

select event_type, doc_page, application_number, 
       document_reference, username, application_year, 
       full_name, date_and_time
  from gdpr_document_manager_audit
 ORDER BY 
   CASE
     WHEN p_sort_col = 'EventType' and p_sort_order = 'asc' THEN event_type 
     WHEN p_sort_col = 'ApplicationYear' and p_sort_order = 'asc' THEN application_year 
     WHEN p_sort_col = 'ApplicationNumber' and p_sort_order = 'asc' THEN application_number 
     WHEN p_sort_col = 'DocumentReference' and p_sort_order = 'asc' THEN doument_reference 
     WHEN p_sort_col = 'Username' and p_sort_order = 'asc' THEN username 
     WHEN p_sort_col = 'Name' and p_sort_order = 'asc' THEN full_name 
   END ASC,
   CASE 
     WHEN p_sort_col = 'DateAndTime' and p_sort_order = 'asc' THEN date_and_time
   END ASC,
   CASE 
     WHEN p_sort_col = 'EventType' and p_sort_order = 'desc' THEN event_type 
     WHEN p_sort_col = 'ApplicationYear' and p_sort_order = 'desc' THEN application_year 
     WHEN p_sort_col = 'ApplicationNumber' and p_sort_order = 'desc' THEN application_number 
     WHEN p_sort_col = 'DocumentReference' and p_sort_order = 'desc' THEN document_reference 
     WHEN p_sort_col = 'Username' and p_sort_order = 'desc' THEN username 
     WHEN p_sort_col = 'Name' and p_sort_order = 'desc' THEN full_name 
 END DESC,
 CASE 
     WHEN p_sort_col = 'DateAndTime' and p_sort_order = 'desc' THEN date_and_time
 END DESC

returns the data you want in the order you want, just without pagination. I'm going to refer to this query as #base_query# in my answer below for simplicity. If so, and given that you you need to support an older version of Oracle, you'd convert that to a paginated query as

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
                a.*, ROWNUM rnum 
           from ( #base_query# ) a 
          where ROWNUM <= ((p_page_index * p_page_size) + p_page_size ) ) 
  where rnum  >= (((p_page_index-1) * p_page_size));

using the calculations you have for the minimum and maximum row that you want returned. You haven't told us what values you are passing for the various parameters. If we guess that p_page_index is 1 for the first page (some people use 0-based indexing), you'd probably actually want

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
                a.*, ROWNUM rnum 
           from ( #base_query# ) a 
          where ROWNUM <= (p_page_index * p_page_size) 
  where rnum  >= (p_page_index-1) * p_page_size + 1;

So if p_page_index = 1 and p_page_size = 10, you'd get rows 1 to 10. If p_page_index = 2 and p_page_size = 10, you'd get rows 11 to 20. etc.