Dear all,

I'm a new one in this forum.
I have found a problem when using ExecuteBath to insert about 86,000 records into Oracle database (Oracle 10g).
I have bit experience on database.

Please see my code below:

public static Connection getConnection() throws SQLException {
Connection conn = null;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// url = "jdbcracle:thin:@machineNameortID"
String url = "jdbcracle:thin:@" + SERVER + ":" + PORT + ":" + SID;
logger.debug("URL = " + url);

conn = DriverManager.getConnection(url, USERNAME, PASSWORD);
// url, userid, password
conn.setAutoCommit(false);

return conn;
}

public static int insert2DB(DataMessage message) {
Connection conn = null;
PreparedStatement pstmt = null;

EmployeeData[] employees = message.getEmployees();
int number = employees.length;

int[] numberDataInserted = {};

String sql = "INSERT INTO EMPLOYEE(RECORD_ID, SENDER_ID, PACKAGE_ID, EMPNO, EMPNAME, EMPLEVEL, EMPBONUS) VALUES (RECORD_ID_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?)";

try {
conn = getConnection();

pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, message.getSenderID());
pstmt.setInt(2, message.getPackageID());

for (int i = 0; i < number; i++) { // error when number = 86,000
pstmt.setString(3, employees[i].getEmpNo());
pstmt.setString(4, employees[i].getEmpName().trim());
pstmt.setString(5, employees[i].getEmpLevel());
pstmt.setInt(6, employees[i].getEmpBonus());

logger.debug("----- Reading " + i + ": " + employees[i].toString());

pstmt.addBatch();
}

numberDataInserted = pstmt.executeBatch();

conn.commit();

} catch (SQLException e) {
logger.error("SQLException: ", e);
} finally {
if (conn != null) {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
logger.error(
"SQLException: Can't close Statement/Connection.",
e);
}

}
}

return numberDataInserted.length;
}

---------------------------
This error is: Exception: java.lang.OutOfMemoryError: Java heap space.



Any one has experience about ExecuteBath, please advice me.



I think that because the number of records inserted is very large so this kind of exception occurred.

Have you know about this problem? Are there any limited number of columns and records also when using ExecuteBath function? Or there is a limit of memory size for this statement?

Please give me some idea about my problem.

Thank you very much,
Hieu Phan.