Re: Got It!

Tom,

Unbelievable. I set a break point before I did the commit in my Java program and then did the queries you suggested. The text showed both the text I sent through the JDBC driver to call the stored procedures, and the implicit cursors (INSERTs, etc) which were used in the PL/SQL. When I stepped past the commit, only the implicit cursors disappeared. The JDBC text stayed.

In Java, record sets, statements, database connections, etc are supposed to do an automatic close when the variable falls out of scope or is otherwise de-referenced. I was using the CallableStatement local to a method which called stored procedures thinking that this behavior would be true when the method ended. The JDBC thin driver apparently opens up a cursor for every CallableStatement and keeps a reference to that statement until an explicit close(). Once I added the statement.close() after the statement.execute(), it worked. I didn’t run into this until now because I was using regular SQL calls rather than PreparedStatements.

I remember complaining to my computer screen (okay I was yelling) the other day that I couldn’t understand why Oracle was using more than a few cursors at a time in my package. Sure enough, now it uses only a few cursors. I could set the OPEN_CURSORS value to 20 and it would work.

Thanks a million for helping me sleuth,

STAN :^) Received on Wed May 05 1999 - 11:33:18 CDT