"java.lang.OutOfMemoryError: Java heap space" due to calling PL/SQL function

[dafti]

Hi,

I have a problem, when calling a Oracle PL/SQL function which returns a ref cursor, providing a large amount of data.
The procedure is called via a org.springframework.jdbc.object.StoredProcedure (processed in usual manner):

Declaring parameters in the constructor, with a RowMapper for the output parameter
Overriding the execute-method with passing the values to the declared parameters, and calling super.execute();

The returned Map is then passed to a text file writing class.

Because there is the DB returns a huge amount of data (the stored procedure takes about 40 minutes), the needed memory of the application raises rapidly until the application throws an OutOfMemory exception: java.lang.OutOfMemoryError: Java heap space

Changing the architecture of the application is not really possible, because it's a framework, which already includes different types of reports, and this should not get messed up. Just extending the heap space also is not a solution, because the amount of data can get even larger.

Is there a possibility to get the Result Set for further processing, instead of working with the Map or List returned?
Would be great, if someone could provide me also an example.

cheers,
David

[rishishehrawat]

From what I have understood your current approach is to first load all the data from database & then start writing to the file. I think you will have to change the approach & use streaming based approach used by Spring batch. Basically read data from database in chunks & write to the file, this will result in memory being released & after reads & write has been done. Also means that you don't need a big java heap to process large amounts of data. Since your SP is already returning a ref cursor IMHO this change should be possible.

[dafti]

Hi,

thank you for the response. I think, I will start reading about Spring Batch. My current approach was, to get the Connection object from the JDBC template, instantiate a CallableStatement, and get the data with old school JDBC way.

cheers

[rishishehrawat]

Actually you need not use Spring batch, Spring batch type approach can be used by you. By this I mean that instead of first reading all rows & then start writing to file, etc, you can read row then write the row, so basically

while (result set has more records) {
read row from database
write row to file
}

By doing this you are decreasing you memory footprint as you are not reading all the records in memory. The records that have already processed can be garbage collected. This essentially is a streaming based approach. In case you are using JdbcTemplate you can have a look at RowCallbackHandler.