This content has been marked as final. Show 5 replies
Your question is not very clear..
If you want to se the output in SQLPLUS, you can use PAUSE and PAGESIZE. Something like..
If you want to fetch using Cursor in PL/SQL, you can use BULK COLLECT with LIMIT
SQL> set pagesize 8 SQL> set pause on
Or if you want pagination you can refer AskTom
SQL> set serverout on SQL> declare 2 cursor c_emp is select * from emp ;--order by will be required 3 type t_emp is table of c_emp%rowtype; 4 t_emp1 t_emp; 5 begin 6 open c_emp; 7 loop 8 fetch c_emp bulk collect into t_emp1 limit 5; 9 dbms_output.put_line(t_emp1.count||' Records Fetched'); 10 exit when c_emp%notfound; 11 end loop; 12 close c_emp; 13 end; 14 / 5 Records Fetched 5 Records Fetched 4 Records Fetched PL/SQL procedure successfully completed.
Edited by: jeneesh on Oct 28, 2012 11:36 AM
Article snippet from - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Pagination with ROWNUM
My all-time-favorite use of ROWNUM is pagination. In this case, I use ROWNUM to get rows N through M of a result set. The general form is as follows:
select * from ( select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from ( your_query_goes_here, with order by ) a where ROWNUM <= :MAX_ROW_TO_FETCH ) where rnum >= :MIN_ROW_TO_FETCH;
FIRST_ROWS(N) tells the optimizer, "Hey, I'm interested in getting the first rows, and I'll get N of them as fast as possible."
:MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
Hope that helps.
I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed.
That CANNOT be guaranteed at all unless the table is READONLY and you use ORDER BY on the query. Even then it cannot be done if by 'query' you mean EXACTLY the same string of characters. Oracle has no memory except the sql cache and with the exact same query (either no bind variables or the same bind variables) will return the exact same results.
If the table is not READONLY then the second (or third, etc) queries could include rows that did not exist before your first query, would not include rows that were deleted after one of your queries and would include rows that were different (updated) between any two of your queries.
If you allow the query to be different then you can use a pagination query (see other replies) based on ROWID or the primary key of the table. Even then, if there are no unique columns you need to use ORDER BY on ALL columns or you could get the same set of rows in a query that you had already gotten in a previous query.
The use of ORDER BY is required to ensure the order of rows. So unless you base the query on ROWID or a unique column value you would need to order all of the columns.
Most likely you are only looking for the simplistic pagination solution already suggested by others.