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
– deval.techie

2013-04-29 05:46:58 +00:00
Commented Apr 29, 2013 at 5:46 — deval.techie
– deval.techie, Commented Apr 29, 2013 at 5:46
Did you manage to get past this?

karansardana
– karansardana

2016-07-08 07:30:11 +00:00
Commented Jul 8, 2016 at 7:30 — karansardana
– karansardana, Commented Jul 8, 2016 at 7:30

Joe · Accepted Answer · 2013-10-08 22:27:01Z

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 8, 2013 at 22:27

Joe

15.6k8 gold badges51 silver badges57 bronze badges

answered Oct 8, 2013 at 22:06

Gayathri

9126 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:51:31Z

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, 2017 at 11:51

CommunityBot

11 silver badge

answered Aug 4, 2016 at 11:57

Manuel Spigolon

13.4k7 gold badges60 silver badges95 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related