• Post Reply Bookmark Topic Watch Topic
  • New Topic

to generate xml file  RSS feed

 
Asif Kunimmal
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Joe Ess
Bartender
Posts: 9441
12
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not a lot of code to work with, but I will tell you that you MUST close every single instance of ResultSet, Statement, PreparedStatement, Connection. . . any resource that has a close method (sockets, IO streams). Make sure to invoke close in a finally block so they will be handled in the event of an exception. Failure to do so WILL cause you problems in any long-lived application.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!