Oracle LONG and LONG RAW Causing “Stream has already been closed” Exception

2019独角兽企业重金招聘Python工程师标准>>>

Oracle LONG and LONG RAW Causing “Stream has already been closed” Exception

"

In short: All LONG or LONG RAW columns have to be retrieved from the ResultSet prior to all the other columns.

重要的事：rs.getBytes(3) 一定要放在其它的rs.getXXX的前面。（找了两天，才发现是这个问题，记在这让后来者少走弯路）

"

Like many old databases, Oracle has legacy data types, which are rather nasty to work with in every day SQL. Usually, you don’t run into wild encounters of LONG and LONG RAW data types anymore, but when you’re working with an old database, or with the dictionary views, you might just have to deal with LONG.

These data types are pretty much the same thing as the “newer” LOB representations:

• LONG and CLOB are somewhat the same thing, except they aren’t
• LONG RAW and BLOB are somewhat the same thing, except they aren’t

Reading LONG or LONG RAW from JDBC causes a “Stream has already been closed” exception

When you have the following schema:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

CREATE TABLE t_long_raw_and_blob (   id        NUMBER(7),   blob1     BLOB,   longx     LONG RAW,   blob2     BLOB,     CONSTRAINT pk_t_long_raw_and_blob PRIMARY KEY (id) );   CREATE TABLE t_long_and_clob (   id        NUMBER(7),   clob1     CLOB,   longx     LONG,   clob2     CLOB,     CONSTRAINT pk_t_long_and_clob PRIMARY KEY (id) );

… you cannot just simply select all columns from JDBC (or other APIs) like this:

1 2 3 4 5 6 7 8 9 10 11 12

try (PreparedStatement s = con.prepareStatement(         "SELECT * FROM t_long_raw_and_blob");      ResultSet rs = s.executeQuery()) {       while (rs.next()) {         System.out.println();         System.out.println("ID    = " + rs.getInt(1));         System.out.println("BLOB1 = " + rs.getBytes(2));         System.out.println("LONGX = " + rs.getBytes(3));         System.out.println("BLOB2 = " + rs.getBytes(4));     } }

If you’re doing the above, you’ll run into something along the lines of:

Caused by: java.sql.SQLException: Stream has already been closed
    at oracle.jdbc.driver.LongRawAccessor.getBytes(LongRawAccessor.java:162)
    at oracle.jdbc.driver.OracleResultSetImpl.getBytes(OracleResultSetImpl.java:708)
    ... 33 more

The “correct” solution would be, to run the following, instead:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

try (PreparedStatement s = con.prepareStatement(         "SELECT * FROM t_long_raw_and_blob");      ResultSet rs = s.executeQuery()) {       while (rs.next()) {         byte[] longx = rs.getBytes(3);           System.out.println();         System.out.println("ID    = " + rs.getInt(1));         System.out.println("BLOB1 = " + rs.getBytes(2));         System.out.println("LONGX = " + longx);         System.out.println("BLOB2 = " + rs.getBytes(4));     } }

In short: All LONG or LONG RAW columns have to be retrieved from the ResultSet prior to all the other columns.

重要的事：rs.getBytes(3) 一定要放在其它的rs.getXXX的前面。（找了两天，才发现是这个问题，记在这让后来者少走弯路）

https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/

转载于:https://my.oschina.net/jms0755/blog/860910