Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

hanging while inserting CLOB

 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We've recently migrated from Oracle 9i to Oracle 10g, and Java code that used to work now causes Oracle to hang.

The code involves inserting a CLOB into a database.

We first insert many other values and an EMPTY_CLOB(), as in the following:



This statement works fine. We then try to insert the CLOB.



A typical value of query is:



This code works fine in Oracle 9i, but in Oracle 10g, Oracle hangs, and we find a lock on the row in the table that we are trying to update.

Some Googling revealed that Oracle 10g has a more efficient way of inserting CLOBs. We set the value of connectionProperties to SetBigStringTryClob=true in the XML that defines DataSource for the JDBC driver, and we used the following code to try to insert the CLOB:



But Oracle still hangs.

We haven't had any problems inserting BLOBs, VARCHAR2s, and other data types into the table. The code for BLOBs is pretty much the same as the original CLOB code, and it works fine.

Any advice?
 
Brandon Grantham
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, I'm having the same hanging issue when inserting a clob into a 10g db using a Preparedstatement. I see the question posted all over the web without a solution. Has anyone come up with a resolution?
Thank you,
Brandon
 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I posted that query a long time ago, so I'm not sure, but I *think* that the solution was to change the Oracle JDBC Driver to ojdbc14.jar. See this page for downloads. Some of the drivers that Oracle provides have bugs.
 
Brandon Grantham
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That is a shame. We are already using that driver version. I also tried the latest version of the JDBC driver and that produced the same results. Thanks for the reply.
 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've found we learned to insert strings into a CLOB field in an Oracle 10g database. You can simply use

update table set field = 'value';

like you would if the field were a varchar2. You don't need to use a PreparedStatement at all.
 
Brandon Grantham
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply.

I finally figured it out. There was not enough connections or the connections were being not being closed. So I used (Partial code)

PrearedStatement stmt = db.getDataSource().getConnetion().preparedStatement(sql, Statement.RETURN_GENERATED_KEYS);

for loop
stmt.setString(i+1, params[i]);

stmt.execute();
stmt.getConnection().close();


This closed the connection and that has resolved the issue for me. Before I was using stmt.close(); which left the connection open.

Thanks again!

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic