CODEWITHSUNDEEP
javajdbcspring-jdbc
mallikarjun
mallikarjun

Reputation: 1862

JDBC fetch size resets to 1 for streaming types from oracle documentation

From Oracle JDBC documentation (https://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28782)

If a column of a result set is of data type LONG, LONG RAW or LOBs returned through the data interface, that is, the streaming types, then JDBC changes the statement row-prefetch setting to 1, even if you never actually read a value of either of these types.

If fetch size is not provided for the ResultSet by default equal to the statement fetch size.

if my ResultSet contains the streaming types, does the result set fetch size also reset in the next trip to DB? If yes how can we keep the result set size?

I am using spring-jdbc in few projects does spring handles this?

Upvotes: 0

Views: 456

Answers (1)

Jean de Lavarene
Jean de Lavarene

Reputation: 3773

If one of the columns you're fetching is a LONG or LONG_RAW then yes the driver will fetch the rows one by one no matter what fetch size you use. In case you're wondering, a "LOB returned through the data interface" is a LOB that you define as a LONG using defineColumnType. There is a connection property called "useFetchSizeWithLongColumn" which, if set to "true", will change this behavior but keep in mind that a LONG or LONG_RAW can be larger than a gigabyte so if you fetch 10 of them you're going to consume a lot of memory in the driver.

Upvotes: 1

Related Questions