In Oracle if you want to limit the records based on ROWNUM(mostly while doing pagination) the between is not going to work.

Below Doesn’t work

 
 SELECT Column1, Column2, ......., Column N  
   FROM TableName
  WHERE ROWNUM Between 5 AND 10; 

The alternate for this is as Follows

 
SELECT * FROM
 (SELECT rows_to_page.*, ROWNUM rnum FROM
   (SELECT Column1, Column2, ......., Column N  
      FROM TableName
     WHERE ROWNUM Between 5 AND 10) rows_to_page
    WHERE ROWNUM < end_offset) 
 WHERE rnum > start_offset;