Exceptions during inserting row with clob column

[ch praveen]

Hi All,

Iam using a clob column in a table, which Iam accessing through java. Sometimes Iam
able to insert rows without any problem. But sometimes various errors are encountered during
insertion into database. Any help is highly appreciated.

platform::
********
oracle 8.1.7
Jbdbc driver:: classes12.jar [found in ora92\jdbc\lib folder of oracle 8]

database structure::
*******************
XLDOCUMENT
----------
PREFIX VARCHAR2(30) not null,
ENGINEID VARCHAR2(40) not null,
INITIALENGINEID VARCHAR2(40) not null,
WFDURL VARCHAR2(255) not null,
WFDNAME VARCHAR2(50) not null,
WFDREVISION VARCHAR2(40) not null,
WFINSTANCEID VARCHAR2(40),
EXPRESSIONNAME VARCHAR2(40) not null,
EXPRESSIONID VARCHAR2(50),
XLDOC CLOB not null


Java method that insert rows in XLDOCUMENT table::
************************************************

public void store (final FlowExpressionId fei, final String sXml)
        throws XlobException
    {
        java.sql.PreparedStatement ps = null;
        log.debug("****Inside store(FlowExpressionId fei, String sXml)");
        log.debug("fei = " + fei + ", sXml = " + sXml);
        
        try
        {
            if (isStored(fei))
            {
            log.debug("As isStored(fei) is true, calling remove(fei)");
            remove(fei);
            }
 
            log.debug("As isStored(fei) is false,insert into xldocument");
            
            //
            // insert the doc
 
            final StringBuffer sb = new StringBuffer();
 
            sb
                .append("INSERT INTO xldocument ")
                .append("(")
                .append(FIELDS)
                .append(") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            
            Connection con = getConnection(); 
            ps = con.prepareStatement(sb.toString());
 
            int i = 1;
 
            ps.setString(i++, this.prefix);
            ps.setString(i++, fei.getEngineId());
            ps.setString(i++, fei.getInitialEngineId());
            ps.setString(i++, fei.getWorkflowDefinitionUrl());
            ps.setString(i++, fei.getWorkflowDefinitionName());
            ps.setString(i++, fei.getWorkflowDefinitionRevision());
            ps.setString(i++, fei.getWorkflowInstanceId());
            ps.setString(i++, fei.getExpressionName());
            ps.setString(i++, fei.getExpressionId());
 
            log.debug("Query for prepared stmt = " + sb.toString());
 
            log.debug("prefix = " + prefix);
            log.debug("fei.getEngineId() = " + fei.getEngineId());
            log.debug("fei.getInitialEngineId() = " + fei.getInitialEngineId());
            log.debug("fei.getWorkflowDefinitionUrl() = " + fei.getWorkflowDefinitionUrl());
            log.debug("fei.getWorkflowDefinitionName() = " + fei.getWorkflowDefinitionName());
            log.debug("fei.getWorkflowDefinitionRevision() = " + fei.getWorkflowDefinitionRevision());
            log.debug("fei.getWorkflowInstanceId() = " + fei.getWorkflowInstanceId());
            log.debug("fei.getExpressionName() = " + fei.getExpressionName());
            log.debug("fei.getExpressionId() = " + fei.getExpressionId());
            
            final java.io.StringReader reader = 
                new java.io.StringReader(sXml);
 
            /*----- Code modification to make it compatible with oracle - Starts */
            
 
            oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(con , false, oracle.sql.CLOB.DURATION_CALL);
Writer writer = newClob.getCharacterOutputStream();
int chunkSize = newClob.getChunkSize();
 
BufferedReader  br = new BufferedReader(reader);
            int bytesRead;
            log.debug("chunkSize for newClob = " + chunkSize);
            char []byteBuffer = new char[chunkSize];
 
            log.debug("***Writing to clob***");
            while ((bytesRead = br.read(byteBuffer)) != -1) {
           
            log.debug("bytesread = " + bytesRead);
            writer.write(byteBuffer, 0, bytesRead);
            
            log.debug("before writer.flush()");
            writer.flush();
            log.debug("after writer.flush()");
            }
       
            log.debug("***Setting clob to Pstmt***");
            /* Is working iff file.length < 4002 */
            /*ps.setCharacterStream( i++, br, (int)file.length());*/
            ps.setClob(i++, newClob);
            
            log.debug("***calls executeUpdate after (Writer for clob <- ) wrtr.flush() and wrtr.close() ***");
           
            log.debug("before writer.close()");
            writer.close();
            log.debug("after writer.close()");
            
            /* Code modification to make it compatible with oracle - Ends -----*/
            
            ps.executeUpdate();
        }
        catch (final Exception e)
        {
            throw new XlobException
                ("update failure for "+fei, e);
        }
        finally 
        {
            close(ps);
            releaseConnection();
        }
    }

Exceptions List::
****************
1]
Caused by: java.sql.SQLException: Io exception: Unexpected packet
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at openwfe.org.xlob.XlobSessionImpl.store(XlobSessionImpl.java:510)
... 10 more

---------

2]
2005-12-22 17:03:55,005 [Thread-31] DEBUG openwfe.org.xlob.XlobSessionImpl - fei.getExpressionId() = 0.0.1.0
2005-12-22 17:03:55,036 [Thread-36] INFO openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() Yes : java.sql.SQLException: Io exception: Bad packet type
2005-12-22 17:03:55,036 [Thread-31] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
2005-12-22 17:03:55,036 [Thread-36] DEBUG openwfe.org.sql.SqlUtils - closeStatement() problem when disposing of sql resources
java.sql.SQLException: Io exception: Size Data Unit (SDU) mismatch
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:648)
at openwfe.org.sql.SqlUtils.closeStatement(SqlUtils.java:91)
at openwfe.org.sql.ds.OwfeDataSource.isConnectionInvalid(OwfeDataSource.java:251)

--------

3]
2005-12-21 19:23:42,995 [Thread-49] DEBUG openwfe.org.xlob.XlobSessionImpl - fei.getExpressionId() = 0.0.0.1.0
2005-12-21 19:23:42,995 [Thread-55] DEBUG openwfe.org.sql.SqlUtils - closeStatement() problem when disposing of sql resources
java.sql.SQLException: Io exception: Bad packet type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
at oracle.jdbc.driver.OracleResultSetImpl.internal_close(OracleResultSetImpl.java:1466)
at oracle.jdbc.driver.OracleResultSetImpl.close(OracleResultSetImpl.java:92)
at openwfe.org.sql.SqlUtils.closeStatement(SqlUtils.java:90)
at openwfe.org.sql.ds.OwfeDataSource.isConnectionInvalid(OwfeDataSource.java:251)
at openwfe.org.sql.ds.OwfeDataSource.getConnection(OwfeDataSource.java:181)

--------

4]
2005-12-21 19:14:58,657 [Thread-13] DEBUG openwfe.org.xlob.XlobSessionImpl - prepareStatement() for SELECT count(*) / ( fei ee/ee ee ee ee __env__ )
2005-12-21 19:14:58,657 [Thread-13] DEBUG openwfe.org.xlob.XlobSessionImpl - Query for prepared stmt = SELECT count(*) FROM xldocument WHERE prefix = ? AND engineId LIKE ? AND initialEngineId LIKE ? AND wfdUrl LIKE ? AND wfdName LIKE ? AND wfdRevision LIKE ? AND wfInstanceId LIKE ? AND expressionName LIKE ? AND expressionId LIKE ?
2005-12-21 19:14:58,657 [Thread-13] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() (openwfe.org.sql.ds.OwfeOracleDataSource)
2005-12-21 19:14:58,657 [Thread-8] DEBUG openwfe.org.sql.ds.OwfeDataSource - releaseConnection()
2005-12-21 19:14:58,657 [Thread-13] INFO openwfe.org.sql.ds.OwfeDataSource - isConnectionInvalid() Yes : java.sql.SQLException: Refcursor value is invalid
2005-12-21 19:14:58,657 [Thread-13] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() closing old connection
2005-12-21 19:14:58,657 [Thread-13] DEBUG openwfe.org.sql.ds.OwfeDataSource - getConnection() failed to close old connection java.sql.SQLException: Protocol violation


Can any one throw light on what occasion, we shall get these type of exceptions.

Thanks,

[stu derby]

Nope, that won't work.

See the Oracle sample code, a complete working example:
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.html
(the LOB datatype example, it actually has both LOBs and CLOBs)