We have got a client that wants a large amount of data in XML format. The Xml is roughly like so :
Employee |- Emp Criminal History |- Emp Address |- Address history |- Emp Partner |- Partner history
This is an overly simplified structure and the actual structure is a few more levels nested. The usual size of the files with xml for a year is about 100 MB
With the archaic system we have in place, the tomcat (web server) and database (oracle) live on different servers. The procedure in oracle takes a from/to date date and spits out a clob which is formed by using Oracle's API to create xml. Eg :
XMLSERIALIZE ( XMLELEMENT( XMLCONCAT( XMLAGG(
To write this data out to disk on the server oracle is on, takes a large amount of time. With the architecture I am bound by, I need a java class to make a call to this proc and then from the clob this proc returns, parse each line so I can simply write it to an xml file. I ran a report for 6 months and that contained about 5 million lines of xml which took about 7 hours to run (just to write data to disk on the oracle server). I did run it overnight via a java class and this morning I had a
java.lang.OutOfMemoryError: Java heap space
I am obviously missing something.
What are the things I could follow to get my run time to reasonable levels given the queries are optimally performing ? I have no idea on the benchmarks for Oracle API - I am assuming since they have been around for a while, the xml api isn't an issue.
Java: 1.5. Oracle: 11.2