Hibernate custom type to avoid 'Caused by: java.sql.SQLException: stream is already closed'

Question

How to write a custom long class to handle long values ​​in Oracle to avoid the following error?

Called: java.sql.SQLException: stream is already closed.

thanks

Answer 1

Oracle recommends that you do not use Long and Long Raw columns (starting with Oracle 8i). They are included in Oracle for hereditary reasons only. If you really need to use them, you must first process these columns before trying to touch any other columns in the ResultSet :

Docs :

When a query selects one or more LONG or LONG RAW columns, the JDBC driver streams these columns to the client. After calling executeQuery or the next, the LONG column data is waiting to be read.

Do not create tables with LONG columns. Instead, use LOB, CLOB, NCLOB, and BLOB columns. LONGs are only supported for backward compatibility. Oracle recommends converting existing LONG columns to LOB columns. LOB columns are much less restrictive than LONG columns.

As for sleep mode - see question .

Answer 2

The following does not answer the original question "how to write a custom long class to handle long values ​​in Oracle", but it may be useful to avoid the "Stream is already closed" error when querying long, raw Oracle columns.

We encountered this error using an outdated database that has no chance of changing the column type. We are using Spring with a hibernate3 factory session and transaction manager. The problem arose when several tasks accessed the DAO at the same time. We use the ojdbc14.jar driver and tried the new version with no luck.

Setting useFetchSizeWithLongColumn = true in the connection properties for the OJDBC driver solved the problem. See OracleDriver API

THIS IS THIN REAL ESTATE ONLY. THIS SHOULD NOT BE USED WITH ANY OTHER DRIVER. If set to true, the performance of retrieving data in SELECT will be improved, but the default behavior for processing LONG columns will be changed to extract multiple rows (prefetch size). This means that enough memory will be allocated to read this data. So if you want to use this property, make sure that the LONG columns you are extracting are not too large or that out of memory. This property can also be set as the java property: java -Doracle.jdbc.useFetchSizeWithLongColumn = true myApplication

Answer 3

This happens in a query on system tables:

 SELECT * FROM all_tab_columns WHERE owner = 'D_OWNER' AND COLUMN_NAME LIKE 'XXX%'; 

Answer 4

I think you will get this message when you try to get the Oracle LONG value from the result set several times.

I had code like:

  rs.getString(i+1) ; if (rs.wasNull()) continue ; set(queryAttr[i], rs.getString(i+1)) ; 

And I started getting "Stream is already closed." mistake. I stopped getting the error when I changed the code to:

  String str = rs.getString(i+1) ; if (rs.wasNull()) continue ; set(queryAttr[i], str) ;