Reputation: 51
I have 5k+ characters to read from each row in oracle DB as CLOB for a select list query . After executing the query using springjdbc the below exception is thrown while trying to convert the CLOB object to String.
public List<MasterData> findByRqstId(int rqstId) {
String sql = "SELECT * FROM MASTER_DATA WHERE REQUEST_ID = ?";
List<MasterData> masterDataList = getSimpleJdbcTemplate().query(sql,
ParameterizedBeanPropertyRowMapper.newInstance(MasterData.class), rqstId);
return masterDataList;
}
Caller Code
masterDataList = masterDataDao.findByRqstId(rqstId);
String outputResponse=null;
if (masterDataList != null && masterDataList.size() > 0) {
for (MasterData mData : masterDataList) {
Clob clob = mData.getOutputResponse();
InputStream in = clob.getAsciiStream();
StringWriter w = new StringWriter();
IOUtils.copy(in, w);
String clobAsString = w.toString();
outputResponse = clob.toString();
}
}
I am able to get the clob object. But when I try converting the object to string using the above code , I am getting connection closed exception.
Exception in thread "main" java.sql.SQLRecoverableException: Close Connection
at oracle.sql.CLOB.getDBAccess(CLOB.java:1421)
at oracle.sql.CLOB.getAsciiStream(CLOB.java:352)
In my knowledge the since the clob object is bigger in size the cache is unable to hold the data for the next operation. The following properties are making no difference.
<property name="connectionProperties" value="defaultRowPrefetch=1000" />
<property name="connectionProperties" value="defaultLobPrefetchSize=500000" />
Upvotes: 1
Views: 2157
Reputation: 1
The reason of the exception is that when you are accessing the blob/clob the connection is already closed.
A solution could be:
Upvotes: 0
Reputation: 51
I am able to retrieve the clob object by convering it as String datatype .Internally I guess Spring takes care of the conversion but its working fine.
Upvotes: 1