(outofmemoryerror: java heap space) when iterating through oracle records...

Question

hello fellow java developers.

I'm having a bit of an issue here. I have code that gets a resultset from an oracle database, prints each row to a file, then gets the next row - and continues till the end of the resultset.

Only this isn't what happens. What happens is that it gets the resultset, starts iterating through the rows, printing to file as it goes, until it runs out of memory - claiming it needs more space on the java heap.

The app is currently running with 2g of memory on the heap and the code breaks at about the 150000th row.

I'm using jodbc6.jar and java 6

Here is an idea of what my code is doing:

Connection conn = DriverManager.getConnection(url,"name","pwd");

conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(strSql);

String strVar_1 = null;

long lCount = 0;
while(rset.next()){
        lCount++;
        if (lCount % 100000 == 0){
            System.out.println(lCount + " rows completed");
        }
      strVar_1 = rset.getString("StringID");  /// breaks here!!!!!!!!!

      if (strVar_1 == null){
        strVar_1 = "";
      }         
      if (!strQuery_1.equals("")){
        out.write(strVar_1 + "\n");
      }
 }
 out.close();

Answer 1

Try below:

Statement stmt = conn.createStatement();
stmt.setFetchSize(someInt);
ResultSet rset = stmt.executeQuery(strSql);

This will control how many records are fetched at a time.

Answer 2

Well maybe another way of dealing with such large data is to keep returning the row and writing it to a file. This way the string buffer does not keep growing and you should be able to write all the records to file system and then read them late