File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes hanging while inserting CLOB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "hanging while inserting CLOB" Watch "hanging while inserting CLOB" New topic
Author

hanging while inserting CLOB

Bob Grossman
Ranch Hand

Joined: Dec 18, 2008
Posts: 69
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

Joined: Jan 08, 2011
Posts: 4
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

Joined: Dec 18, 2008
Posts: 69
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

Joined: Jan 08, 2011
Posts: 4
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

Joined: Dec 18, 2008
Posts: 69
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

Joined: Jan 08, 2011
Posts: 4
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!

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: hanging while inserting CLOB