java.sql.SQLRecoverableException while fetching CLOB data using Hibernate + oracle 11g

Question

I am getting "java.sql.SQLRecoverableException: Closed Connection", exception while fetching CLOB data from Oracle 11g using Hibernate in java web application. For DB connectivity I have implemented Tomcat 7 dataource.

Below is my source where I am getting an Exception :

java.sql.Clob reqClob= userBean.getRequestData();
Reader clbReader = reqClob.getCharacterStream();

In above Code while executing "clob.getCharacterStream()", I am getting below Exception :

java.sql.SQLRecoverableException: Closed Connection
at oracle.sql.CLOB.getDBAccess(CLOB.java:1389)
at oracle.sql.CLOB.getCharacterStream(CLOB.java:309)
at org.hibernate.lob.SerializableClob.getCharacterStream(SerializableClob.java:41)

Please note that, I am able to get the data from userBean for other values.

Below is the app version I have used in application:

jdk1.6.0_33 (64bit version)  
hibernate3.jar 
ojdbc6.jar 
Oracle 11g 11.2.0.1.0 - (64bit version)

One strange behavior is I am getting this issue once I have started using TOMCAT 7 Datasource.

Below is Datasource code from Context.xml :

<Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" 
factory="oracle.jdbc.pool.OracleDataSourceFactory" name="jdbc/XXXX" password="XXXXX" 
type="oracle.jdbc.pool.OracleDataSource" 
url="jdbc:oracle:thin:@xx.xx.xx.xxxx:xxxx:xxxxxx" user="username"
connectionProperties="SetBigStringTryClob=true;" maxActive="20"
maxIdle="10" maxWait="-1" validationQuery="select 1 from dual" />

I have tried all possible parameters for in above code, but it didn't work.

Thanks in advance for all your help, guys...

Please reply me guys....its show stopper for me.. :( – deval.techie Apr 29 '13 at 05:46 — deval.techie, Apr 29 '13 at 05:46
Did you manage to get past this? – karansardana Jul 08 '16 at 07:30 — karansardana, Jul 08 '16 at 07:30

score 2 · Answer 1 · edited Oct 08 '13 at 22:27

2

I had similar problem, I changed Clob to String as below:

String clobAsString = clob.getSubString(1, (int)clob.length());

Make sure you perform this when the connection is alive. Like in DAO. This has to be performed within the transaction which retrieves Clob element from DB.

edited Oct 08 '13 at 22:27

Joe

15,205
8
49
56

answered Oct 08 '13 at 22:06

Gayathri

894
6
19

score 2 · Answer 2 · edited May 23 '17 at 11:51

2

I had the same problem today and always when i was calling getSubString or getCharacterStream i had the Closed Connection error.

I solved with annotation and removed the Clob type:

@Column(name = "CL_JSON_OUT", nullable = false)
private Clob jsonOut;

to:

@Lob
@Column(name = "CL_JSON_OUT", nullable = false)
private String jsonOut;

This post helped me

edited May 23 '17 at 11:51

Community

1
1

answered Aug 04 '16 at 11:57

Manuel Spigolon

11,003
5
50
73

java.sql.SQLRecoverableException while fetching CLOB data using Hibernate + oracle 11g

2 Answers2

Linked