• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

xml file from db

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Maximum open cursors exceeded" almost certainly means you aren't closing your ResultSets. (This isn't anything to do with XML, you should always close a ResultSet after you finish using it.)

And the remark that you are only using one result set can't be right. You have to be using one ResultSet per query. It's possible that you always use the same variable to refer to these ResultSets, but you're still creating thousands of them. (And as I said, probably not closing them.)

Sorry, I don't know anything about XSU so I can't suggest whether that might solve your problem.
 
Ranch Hand
Posts: 290
Oracle Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oracle 9i provides PL/SQL packages for generating XML. Try using them.

Regards
Ahmad
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic