This is a web application in
j2ee. we r having problem in accessing 1000s of data from the oracle 9i db from our application.
The problem is in generating report from the application===> This is nothing but generating an xml file from the application and an xslt file which accepts input as the generated xml file. Cocoon accepts the xslt file and gives the output as html or pdf reports.
Now the problem is while generating xml file. The xml is getting created by using 8 queries from the database. In the previous code what was done is like writing each result of the query into lists and picking one by one data and writing it into a file using writeBytes. Hence while generating for huge amount of data it is getting stucked in between and the cpu takes 100% usage.
Now We made some changes in the code that we created one Document(DOM concept) and added the result of each query into the parent node. (The query is like nested loop ie the result should be appended in between the first query.... ). When generating the xml file for huge data(for less data working fine) we r getting an exception as "java.sql.SQLException: ORA-01000: maximum open cursors exceeded". Then we tried with only one prepared statement and one resultset and after that we have tried with "CachedRowSet" (new feature in jdk1.5 which closes the result set after executing query), then tried with increasing the number of open_cursors in oracle.
The code vl look like below
execute prepare statement(query1)
while(resultset1){
create element
add data to that element
appnd to parent
execute prepare statement(query2)
while(resultset2){
create element
add data to that element
appnd to parent
execute prepare statement(query2)
...
...
...
}
}
We came to know that there is another methed XSU(Xml Sql Utility(Coming with oracle) which directly generates xml
string.. but not in the proper order)
Plz help me by giving a proper methed or way to solve this problum... Or Can we solve this with XSU ...
Also plz tell me abt anyother methed that we can follow to solve this problum.