Fetching ouput in groups

[amolik]

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

[TimHall]

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...

[TimHall]

Missed out the:
ORDER BY id
Sorry!

[amolik]

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

[TimHall]

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...

[TimHall]

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...

[TimHall]

Now I can't spell comedy!

Help me OCPWANNABE!!

[amolik]

Hey Thanks Tim

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

Amol