14

How do I write a custom Long class to handle long values in Oracle, to avoid the following error?

Caused by: java.sql.SQLException: Stream has already been closed.

Thanks

Perception
  • 79,279
  • 19
  • 185
  • 195
user1316131
  • 171
  • 1
  • 3
  • 5

4 Answers4

12

Oracle recommends not using Long and Long Raw columns (since Oracle 8i). They are included in Oracle only for legacy reasons. If you really need to use them, the you should first handle these columns before attempting to touch any other columns in the ResultSet:

Docs:

When a query selects one or more LONG or LONG RAW columns, the JDBC driver transfers these columns to the client in streaming mode. After a call to executeQuery or next, the data of the LONG column is waiting to be read.

Do not create tables with LONG columns. Use large object (LOB) columns, CLOB, NCLOB, and BLOB, instead. LONG columns are supported only for backward compatibility. Oracle recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns.

As for hibernate - see this question.

Community
  • 1
  • 1
Anonymous
  • 18,162
  • 2
  • 41
  • 64
  • I tried by implementing hibernate UserType for long but still same error Caused by: java.sql.SQLException: Stream has already been closed.Can anybody suggest me in this to avoid this error ? – user1316131 Apr 17 '12 at 12:10
  • Just before trying anything more sophisticated... Have you tried the newer JDBC driver? – Anonymous Apr 17 '12 at 12:21
  • And one more thing, Hibernate gets the columns from the `ResultSet` as they are needed. Would it be OK to get the `LONG RAW` column first? (Oracle assumes that columns are accessed in the order from left to right and streams are closed when columns after stream columns are accessed) – Anonymous Apr 19 '12 at 18:31
7

The following doesn't answer the original question 'how to write a custom Long class to handle long values in Oracle' but may be helpful to avoid the 'Stream has already been closed' error when querying Oracle long raw columns.

We faced this error using a legacy database with no chances of changing the column type. We use Spring with hibernate3 session factory and transaction manager. The problem occurred when more than one task were accessing the DAO concurrently. We are using ojdbc14.jar driver and tried a newer one with no luck.

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

THIS IS A THIN ONLY PROPERTY. IT SHOULD NOT BE USED WITH ANY OTHER DRIVERS. If set to "true", the performance when retrieving data in a 'SELECT' will be improved but the default behavior for handling LONG columns will be changed to fetch multiple rows (prefetch size). It 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 retrieving are not too big or you may run out of memory. This property can also be set as a java property : java -Doracle.jdbc.useFetchSizeWithLongColumn=true myApplication

Pablo
  • 1,604
  • 16
  • 31
2

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

I had code like:

        rs.getString(i+1) ;
        if (rs.wasNull()) continue ;

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

And I started getting the "Stream has already been closed." error. 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) ;
John Neville
  • 31
  • 2
  • It's apparently worse than that; Lukas Eder describes [in his blog post](https://blog.jooq.org/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/) that Long data needs to be fetched specifically before all other data in the same query results. But this is definitely a useful addition to that information. – Nyerguds Jun 08 '23 at 11:17
-1

This happens in a query of system tables:

SELECT * FROM all_tab_columns
WHERE owner = 'D_OWNER' AND COLUMN_NAME LIKE 'XXX%';
David Cain
  • 16,484
  • 14
  • 65
  • 75
steve thiboutot
  • 1