Oracle/OJDBC BLOB update problem on non-existing rows?

Question

I ran into very peculiar problem with BLOBs in Oracle. I'm using OracleXE 10g (10.2.0.1.0 version of database), and tried it with ojdbc14_g drivers version 10.2.0.1.0, 10.2.0.4.0 and 10.2.0.5.0. The same thing happens always. I think it's something with the drivers based on what I read so far on the various forums, but I'm not sure... And this is the problem:

I have this piece of code, that prepares statement to update two blobs, which are actually zip archives:

File fRst = new File("archive1.zip");
File fPro = new File("archive2.zip");
//...
statement = "UPDATE CURR_STATE" + 
                    " SET ZIP_RST=?, ZIP_PRO=?" +
                    " WHERE SERIAL_NUMBER=" + "'" + serialNo + "'" + " AND" +
                    " YEAR_MONTH=" + "'" + yearMonth + "'";
pstmt = this.connection.prepareStatement(statement);
FileInputStream isR = new FileInputStream(fRst);
FileInputStream isP = new FileInputStream(fPro);
pstmt.setBinaryStream(1, isR, (int) fRst.length());
pstmt.setBinaryStream(2, isP, (int) fPro.length());

int no = pstmt.executeUpdate();
System.out.println("rows: " + no);
this.connection.commit();
pstmt.close();

I was testing the case of update where given record does not exist in the table. if these two zip files are smaller in size (like 2, 5 or 10KB), line:

int no = pstmt.executeUpdate();

returns 0 rows updated, which is expected considering that row defined in WHERE clause does not exist. However, if zip files are a bit bigger (30, 40KB), executeUpdate() throws SQLException with various messages, like:

java.sql.SQLException: Io exception: Software caused connection abort: socket write error
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:363)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1142)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1278)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3498)

or

java.sql.SQLException: No more data to read from socket

or

java.sql.SQLException: OALL8 is in an inconsistent state

These exceptions effectively kill the underlying socket connection, so it becomes unusable. Funny thing is, if the row exists in the table, everything works fine, update executes with no problem, returning 1 as number of updated rows.

I wonder if anybody has already encountered this strange behaviour, and if there's any bypass for this? (except the obvious one - to check if the row exists :) )

Thanks.

With JDK 1.6, you're supposed to use ojdbc6.jar (instead of ojdbc14.jar). — Codo
– Codo, Commented Sep 1, 2011 at 12:46
@Codo Ok, I'll try, but ojdbc6 is for Oracle 11g, I'm using 10g. Do you know that it is backwards compatible? Have you tried it? — Less
– Less, Commented Sep 1, 2011 at 12:55
The different drivers (ojdbcxxx.jar and OCI.dll) are more or less forward and backward compatible. But only a test can show if it works in your case. Using ojdbc6 with Oracle 10g shouldn't be worse than using ojdbc14 with JDK 6. — Codo
– Codo, Commented Sep 1, 2011 at 16:18

Matthew Watson · Accepted Answer · 2011-09-01 11:09:01Z

3

java.sql.SQLException: No more data to read from socket

Whenever I've seen this, its because the oracle server you are connected to has crashed ( not the instance ).

Have you checked your alert log while this is happening?

answered Sep 1, 2011 at 11:09

Matthew Watson

14.3k9 gold badges65 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

WW. Over a year ago

It's probably the dedicated process that has crashed, not the server.

Less Over a year ago

@matthew-watson I just checked it and in the time I was reproducing this errors this is found in log: Thu Sep 01 10:14:56 2011 Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3492.trc: ORA-00600: internal error code, arguments: [12333], [254], [64], [80], [], [], [], []

Less Over a year ago

Seems that there is no log after this... Then I restarted the db server after some time, and this is the very nex line (2 hours later?): Thu Sep 01 12:57:50 2011 Shutting down instance: further logons disabled

Ingo Over a year ago

Oops - yet another Oracle bug, it seems. Prepare to get familiar with OPatch, or upgrade to the next version of the "industrial strength" RDBMS or better, if you can, use another RDBMS. Looks like Oracle Corp. invests most in lawsuits against competitors lately ....

Matthew Watson Over a year ago

Yep, if you're getting ORA-600, then its time to raise a SR with oracle.

Collectives™ on Stack Overflow

Oracle/OJDBC BLOB update problem on non-existing rows?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related