Fetching ouput in groups
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Fetching ouput in groups

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi All

    Is there any way where I can send my output of the query in the bunch of records.
    e.g.
    I have a query "select * from emp" which returns 15000 rows and my java programmer has to take this output into XML file and parse it and display it (exact XML thing I dont know) However he found that XML size becomes too big and he cannot parse it earliest and that results in delayed display.
    Now he wants to display first 500 and then next 500 record and like that. He wants that the query should reurn next 500 records like that.
    He gives me example of google.com where output is given in groups. Can anyone give more information on this

    Amol


  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    Sounds like a job for SAX

    Hi.

    Sounds like a job for SAX. The DOM parser works for a whole document, hence problems when documents get too big. The SAX parser streams through large documents so you don't have the problem of parsing a massive file in one go. I'm no expert but this is what I would do assuming you expect the user to want all the data!

    Assuming you don't expect people to want all the data the paging idea is probably the best. That way you can still use DOM. How you do this depends on the table(s) you are querying. Lets say you have a sequence for a primary key you could do something like:

    SELECT id, data
    FROM table1
    WHERE id > ?
    AND rownum < 20

    Where ? is 0 for the "first page", or the last id used to build the previous page.

    If this is not possible in your circumstances you could try a messier solution like:

    First Page:
    Open a cursor with all data.
    Process the first 20 rows and quit.

    Second Page:
    Open a cursor with all data.
    Read the first 20 and do nothing.
    Process the second 20 rows and quit.

    Etc.

    Therefore the start row is always ((page - 1) * 20) + 1 or something like that.

    I've made use of both of these methods in pl/sql and ASP. Never tried in Java but it shouldn't be a problem. Just make sure you don't reprocess or miss out rows at the page-change.

    Because the second method may not have a fixed order like the sequence example there is a possibility of the paging being innaccurate, allowing reprocessing of rows depending on what insert, updates and deletes have happened.

    I'd be interested to know if anyone else has some other methods!

    Cheers

    Tim...

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    Whoops!

    Missed out the:
    ORDER BY id
    Sorry!

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    Hi Tim and All

    Thanks for your immediate and comprehensive reply. Howeverit will be very nice if you can explain me using the following case. Take our regular scott tiger schema .. there are 14 records

    select empno, ename from emp;

    o/p :

    EMPNO ENAME
    ----- ------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 pravin
    7902 FORD
    7934 MILLER

    14 rows selected.

    I run it using your method as I cannot allow more than 10 rows in one page.

    SELECT empno, ename
    FROM emp
    WHERE empno < 7777
    AND rownum < 10
    ORDER BY empno;

    EMPNO ENAME
    ----- ------
    125 ajay
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE

    7 rows selected.

    And now how do I go next 7 records. I don't know about anything in XML or DOM. I am using Stored Procedures and functions to retrive the data (This function is called from Java Code).DO I have to pass the value at 7777 from Procedure. Or that has to be kept incrementing by a certain count.

    I guess that will help you to answer my query ?

    Thanks in Advance

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    My Mistake

    Hi.

    Sorry I've lead you astray. That'll teach me to type before I think

    The SQL should have read:

    SELECT a.empno, a.ename
    FROM (SELECT empno, ename
    FROM emp
    ORDER BY empno) a
    WHERE id > ?
    AND rownum <= 10;

    Like:

    SELECT a.empno, a.ename
    FROM (SELECT empno, ename
    FROM emp
    ORDER BY empno) a
    WHERE id > 0
    AND rownum <= 10;

    EMPNO ENAME
    ----- ------
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7844 TURNER

    Second select would be:

    SELECT a.empno, a.ename
    FROM (SELECT empno, ename
    FROM emp
    ORDER BY empno) a
    WHERE id > 7844
    AND rownum <= 10;

    7876 ADAMS
    7900 pravin
    7902 FORD
    7934 MILLER

    Cheers

    Tim...

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    Commedy of Errors!

    I've done it again. They should be:

    SELECT a.empno, a.ename
    FROM (SELECT empno, ename
    FROM emp
    ORDER BY empno) a
    WHERE empno > 0
    AND rownum <= 10;

    I forgot to replace the ID with EMPNO.

    I'm sure I'm gonna get loads of abuse now. Duck and cover!

    Tim...

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    Aaaarrrggghhh!

    Now I can't spell comedy!

    Help me OCPWANNABE!!

  8. #8
    Join Date
    Dec 2000
    Posts
    255
    Hey Thanks Tim

    That was really helpful .. I ll try to implement this into the Stored Procedures

    Amol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width