Clob trim leaves open cursors

java-programmer, Clob trim leaves open cursors Hi,

I have a program which inserts/updates hundreds of clob values in an
Oracle database table. I'm getting a "maximum open cursors" error
when I run this program:

ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_LOB", line 527

To troubleshoot, I tried checking the v$open_cursor table periodically
during the program run by running this SQL query:

select user_name, sql_text from v$open_cursor

The vast majority of the open cursors had the following sql_text
value:

begin dbms_lob.trim (:1, :2); end;

I ran this program before without problems; the only major thing that
I've changed is to add a clob.trim() call, which I realized was
necessary in case the new clob value was longer than the old clob
value.

Below is the method that's being called repeatedly, where
"retrieveClobQuery" is an SQL query to select the clob value, and
"clobData" is the new clob value to be inserted. The same Connection
(conn) is used throughout. A new Statement is created and closed each
time. (I tried rewriting the code to use the same Statement
throughout, but it didn't seem to make a difference.)

public void singleClobUpdate(String retrieveClobQuery, String
clobData) {
Statement stmt = null;

try {
conn.setAutoCommit(false);
stmt = conn.createStatement();

// Use "for update" parameter to lock the row
ResultSet rs = stmt.executeQuery(retrieveClobQuery + " for
update");

if (rs.next()) {
// Get clob value and cast to oracle.sql.CLOB
CLOB clob = (CLOB)rs.getObject(1);

// Write the data to the clob object
Writer writer = clob.getCharacterOutputStream();
writer.write(clobData);
writer.flush();
writer.close();

// trim clob in case new clob contains less data than
previous one
clob.trim(clobData.length());
}

// Commit changes to release the lock
conn.commit();
conn.setAutoCommit(true);
stmt.close();
}
catch (Exception e) {
e.printStackTrace();
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException e) {}
}
}
}

Any help would be greatly appreciated.

Thanks!

java-programmer >> Clob trim leaves open cursors Bert wrote:
>
> public void singleClobUpdate(String retrieveClobQuery, String
> clobData) {
> Statement stmt = null;
>
> try {
> conn.setAutoCommit(false);
> stmt = conn.createStatement();
>
> // Use "for update" parameter to lock the row
> ResultSet rs = stmt.executeQuery(retrieveClobQuery + " for
> update");
>
> if (rs.next()) {
> // Get clob value and cast to oracle.sql.CLOB
> CLOB clob = (CLOB)rs.getObject(1);
>
> // Write the data to the clob object
> Writer writer = clob.getCharacterOutputStream();
> writer.write(clobData);
> writer.flush();
> writer.close();
>
> // trim clob in case new clob contains less data than
> previous one
> clob.trim(clobData.length());
> }
>
> // Commit changes to release the lock
> conn.commit();
> conn.setAutoCommit(true);
> stmt.close();
> }
> catch (Exception e) {
> e.printStackTrace();
> if (stmt != null) {
> try {
> stmt.close();
> }
> catch (SQLException e) {}
> }
> }
> }
>
> Any help would be greatly appreciated.
>

This may not be your problem, but it is good practice to close your
resources in a finally clause. Also, if an exception occurs in the
above code, the connection is never committed and the auto commit status
is not restored. Finally, close everything, including the result set.
You may have read the javadoc that says that closing the statement
closes the result set, but the implementor of the JDBC driver may have
missed that section :)

Ray

java-programmer >> Clob trim leaves open cursors email***@***.com (Bert) wrote in message news:<email***@***.com>...
> Hi,
>
> I have a program which inserts/updates hundreds of clob values in an
> Oracle database table. I'm getting a "maximum open cursors" error
> when I run this program:
>
> ORA-01000: maximum open cursors exceeded
> ORA-06512: at "SYS.DBMS_LOB", line 527
>
> To troubleshoot, I tried checking the v$open_cursor table periodically
> during the program run by running this SQL query:
>
> select user_name, sql_text from v$open_cursor
>
> The vast majority of the open cursors had the following sql_text
> value:
>
> begin dbms_lob.trim (:1, :2); end;
>
> I ran this program before without problems; the only major thing that
> I've changed is to add a clob.trim() call, which I realized was
> necessary in case the new clob value was longer than the old clob
> value.
>
> Below is the method that's being called repeatedly, where
> "retrieveClobQuery" is an SQL query to select the clob value, and
> "clobData" is the new clob value to be inserted. The same Connection
> (conn) is used throughout. A new Statement is created and closed each
> time. (I tried rewriting the code to use the same Statement
> throughout, but it didn't seem to make a difference.)
>
> public void singleClobUpdate(String retrieveClobQuery, String
> clobData) {

what version of oracle are you using? (of course, make sure you are
using the newest jdbc driver for that version of the dbms. also --
thick/thin client?)

having a sql_text in v$open_cursor can be misleading. the pl/sql
engine may keep it in there cached even if you close the statement.
if you comment out the clob.trim() call, then run again, do your open
cursors stop increasing? you may want to test by looping the method
lots of times to see if you can reach max cursors anyway (i.e., even
with the trim() call commented out). if you can, then the problem is
somewhere else, not just the trim() call.

java-programmer >> Clob trim leaves open cursors Hi,

Thanks for the responses and advice. The database is on Oracle 9i and
I'm using the Oracle 9i v9.0.1 thin JDBC driver for Mac OS X. As far
as I can tell, this is the latest version for Mac OS X available on
the Oracle/JDBC download website.

I added a line to close the ResultSet, but it didn't seem to make a
difference. When I comment out the clob.trim() line, leaving
everything else exactly the same, the program runs fine. I did a
select from the v$open_cursor table during that program run, and there
were only a few open cursors (none of them had sql_text "begin
dbms_lob.trim (:1, :2); end;"). At the time of the select, the
program had already inserted >3000 clobs (the maximum open cursors is
500) and was still inserting more. The program finished successfully
having inserted 5800+ clobs.

So I am stumped. If anyone has other suggestions, I'd be very glad to
hear them.

java-programmer >> Clob trim leaves open cursors email***@***.com (Bert) wrote in message news:<email***@***.com>...
> Hi,
>
> Thanks for the responses and advice. The database is on Oracle 9i and
> I'm using the Oracle 9i v9.0.1 thin JDBC driver for Mac OS X. As far
> as I can tell, this is the latest version for Mac OS X available on
> the Oracle/JDBC download website.
>
> I added a line to close the ResultSet, but it didn't seem to make a
> difference. When I comment out the clob.trim() line, leaving
> everything else exactly the same, the program runs fine. I did a
> select from the v$open_cursor table during that program run, and there
> were only a few open cursors (none of them had sql_text "begin
> dbms_lob.trim (:1, :2); end;"). At the time of the select, the
> program had already inserted >3000 clobs (the maximum open cursors is
> 500) and was still inserting more. The program finished successfully
> having inserted 5800+ clobs.
>
> So I am stumped. If anyone has other suggestions, I'd be very glad to
> hear them.

i don't know what the problem might be. it would be a long shot to
guess that it's because you do the trim() after flushing the
characterstream. i doubt that's the problem tho. something else you
might want to try is, instead of doing the trim(), before you write
the clob, execute an update, setting your clob column to empty_clob().
this way you shouldn't need to trim it afterwards.