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

From: The Nomad <nobody_at_nowhere.com>
Date: Wed, 31 Oct 2001 15:30:35 GMT
Message-ID: <vOUD7.551995$8c3.90269671@typhoon.tampabay.rr.com>

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

---

I'm getting the following exception:

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());

    }
    return rtn;
  } Received on Wed Oct 31 2001 - 09:30:35 CST