Help - ORA-22990: LOB locators cannot span transactions - JDBC Oracle 8.1.7

Hey all,

I've been fighting to two days to get this to work properly, and I can't seem to find the right combination of things that will make everything right in the world.

It seems as if the con.commit() isn't working to commit the transaction.

Thanks for any help you could provide,

           Marc

Line 82 is the os.flush() call shown below.

java.io.IOException: ORA-22990: LOB locators cannot span transactions  at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531)  at
oracle.jdbc.driver.OracleBlobOutputStream.flushBuffer(OracleBlobOutputStream
.java:179)

 at
oracle.jdbc.driver.OracleBlobOutputStream.close(OracleBlobOutputStream.java: 152)
 at
com.company.repository.EntryOracleBinaryAttr.save(EntryOracleBinaryAttr.java :82)
... snip...

Here is the method and details:

  private static final String insSql = "begin INSERT INTO THETABLE(TREEENTRYID, ATTRCATEGORY, ATTRID, ATTRBINARYVALUE) VALUES(?,?,?,empty_blob()) return ATTRBINARYVALUE into ?; end;";

  private static final String updSql = "begin UPDATE THETABLE SET ATTRBINARYVALUE = empty_blob() WHERE TREEENTRYID = ? AND ATTRCATEGORY = ? AND ATTRID = ? RETURN ATTRBINARYVALUE INTO ?; end;";

  private static final String CNTSQL = "{call GETDOESEXIST(?,?,?,?)}";

/*

The GETDOESEXIST simply looks like this:

CREATE OR REPLACE PROCEDURE GETDOESEXIST(ITREEID IN THETABLE.TREEENTRYID%TYPE, IATTRCAT IN THETABLE.ATTRCATEGORY%TYPE, IATTRID IN THETABLE.ATTRID%TYPE, OCOUNT OUT INTEGER) AS BEGIN SELECT COUNT(*) INTO OCOUNT FROM THETABLE WHERE TREEENTRYID = ITREEID AND ATTRCATEGORY = IATTRCAT AND ATTRID = IATTRID; END;
/

*/

...snip...

  public int save(Connection con) throws SQLException, RepositoryException {     int rtn = -1;
    int isThere = 0;
    try {

      // Is It There?
      CallableStatement iitSt = con.prepareCall(CNTSQL);
      try {
        iitSt.setInt(1, (int)getTreeId());
        iitSt.setInt(2, (int)getAttrCategory());
        iitSt.setInt(3, (int)getAttrId());
        iitSt.registerOutParameter(4, java.sql.Types.INTEGER);
        iitSt.executeUpdate();
        isThere = iitSt.getInt(4);
        if ( iitSt.wasNull() ) {
          isThere = 0;
        }
      } finally {
        iitSt.close();
      }

      // Keep current autoCommit setting
      boolean autoComm = con.getAutoCommit();
      // Set autoCommit OFF - we will commit ourselves
      con.setAutoCommit(false);
      try {
        BLOB theBlob = null;
        if (isThere == 0) {
          // It's not there - insert the BLOB
          OracleCallableStatement stmt =
(OracleCallableStatement)con.prepareCall(insSql);
          try {
            stmt.setInt(1, (int)getTreeId());
            stmt.setInt(2, (int)getAttrCategory());
            stmt.setInt(3, (int)getAttrId());
            stmt.registerOutParameter(4, java.sql.Types.BLOB);
            stmt.executeUpdate();
            BLOB blob = (BLOB) stmt.getBLOB(4);
            InputStream is = new ByteArrayInputStream(getBinaryValue());
            OutputStream os = blob.getBinaryOutputStream();
            byte[] buf = new byte[blob.getBufferSize()];
            int length = 0;
            while ((length = is.read(buf)) != -1) {
              os.write(buf, 0, length);
            }
            // Flush, close and commit work.
            os.flush();
            os.close();
            con.commit();  // Commit
            rtn = 0;
            isDirty = false;
          } finally {
            stmt.close();
          }
        } else {
           // It is there - update in place...
          OracleCallableStatement stmt =
(OracleCallableStatement)con.prepareCall(updSql);
          try {
            stmt.setInt(1, (int)getTreeId());
            stmt.setInt(2, (int)getAttrCategory());
            stmt.setInt(3, (int)getAttrId());
            stmt.registerOutParameter(4, java.sql.Types.BLOB);
            stmt.executeUpdate();
            BLOB blob = (BLOB) stmt.getBLOB(4);
            InputStream is = new ByteArrayInputStream(getBinaryValue());
            OutputStream os = blob.getBinaryOutputStream();
            byte[] buf = new byte[blob.getBufferSize()];
            int length = 0;
            while ((length = is.read(buf)) != -1) {
              os.write(buf, 0, length);
            }

            // Flush, close and commit work.
            os.flush();
            os.close();
            con.commit();
            rtn = 0;
            isDirty = false;
          } finally {
            stmt.close();
          }
        }
      } finally {
        con.setAutoCommit(autoComm);
      }
    } catch (IOException ex) {
      ex.printStackTrace();
      throw new RepositoryIOException(ex.toString());