clearing batch preparedstatements

Question

I have a java application which read files and writes to oracle db row by row. We have come across a strange error during batch insert which does not occur during sequential insert. The error is strange because it occurs only with IBM JDK7 on AIX platform and I get this error on different rows every time. My code looks like below:

prpst = conn.prepareStatement(query);
while ((line = bf.readLine()) != null) {
  numLine++;
  batchInsert(prpst, line);
  //onebyoneInsert(prpst, line);
}

private static void batchInsert(PreparedStatement prpst, String line) throws IOException, SQLException {
  prpst.setString(1, "1");
  prpst.setInt(2, numLine);
  prpst.setString(3, line);
  prpst.setString(4, "1");
  prpst.setInt(5, 1);
  prpst.addBatch();
  if (++batchedLines == 200) {
    prpst.executeBatch();
    batchedLines = 0;
    prpst.clearBatch();
  }
}

private static void onebyoneInsert(PreparedStatement prpst, String line) throws Exception{
  int batchedLines = 0;
  prpst.setString(1, "1");
  prpst.setInt(2, numLine);
  prpst.setString(3, line);
  prpst.setString(4, "1");
  prpst.setInt(5, 1);
  prpst.executeUpdate();
}

I get this error during batch insert mode :

java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10345)

I already know why this Ora error occurs but this is not my case. I am nearly sure that I am not setting some large data to a smaller column. May be I am hitting some bugs in IBM jdk7 but could not prove that. My question if there is a way that I can avoid this problem ? One by one insert is not an option because we have big files and it takes too much time.

score 0 · Answer 1 · answered Aug 03 '15 at 10:10

Try with

prpst.setInt(5,new Integer(1))

What is the type of variable "numLine"? Can you share type of columns corresponding to the fields you set in PreparedStatement? Try once by processing with "onebyoneInsert". Share the output for this case. It might help identifying root cause. Also print value of "numLine" to console.

clearing batch preparedstatements

1 Answers1