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:
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:
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:
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.
That’s nasty
Indeed! Some sort of low level Oracle protocol flaw has leaked outside of the JDBC API, which is very unfortunate. We don’t care about these details. We should be able to fetch resources in any order.
In jOOQ,
we’ve fixed this issue #4820, so you can run your statement and order the columns in whatever order you want them to be:
DSL.using(configuration)
.select(
T_LONG_RAR_AND_BLOB.ID,
T_LONG_RAR_AND_BLOB.BLOB1,
T_LONG_RAR_AND_BLOB.LONGX,
T_LONG_RAR_AND_BLOB.BLOB2
)
.from(T_LONG_RAR_AND_BLOB)
.fetch();
jOOQ will internally reorder the columns when fetching them from the
ResultSet
, transparently.
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder