This discussion is archived
5 Replies Latest reply: Nov 16, 2012 10:40 PM by 852595 RSS

Fetch records from employee table

852595 Newbie
Currently Being Moderated
Hi All,
I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.


1-5 records
6-10 records
11-15 records
16-20 records
.
.
.
.
46-50 records

Any one can give the query.

Thanks
  • 1. Re: Fetch records from employee table
    jeneesh Guru
    Currently Being Moderated
    Your question is not very clear..

    If you want to se the output in SQLPLUS, you can use PAUSE and PAGESIZE. Something like..
    SQL> set pagesize 8
    SQL> set pause on
    If you want to fetch using Cursor in PL/SQL, you can use BULK COLLECT with LIMIT
    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.
    Or if you want pagination you can refer AskTom

    Edited by: jeneesh on Oct 28, 2012 11:36 AM
  • 2. Re: Fetch records from employee table
    Etbin Guru
    Currently Being Moderated
    It's called pagination:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:127412348064
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:76812348057
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:20663081751269

    Regards

    Etbin
  • 3. Re: Fetch records from employee table
    ranit B Expert
    Currently Being Moderated
    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;
    where
    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.
    Ranit B.
  • 4. Re: Fetch records from employee table
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 5. Re: Fetch records from employee table
    852595 Newbie
    Currently Being Moderated
    Thanks Every one.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points