Explicitly Freeing Cursors in 10g?

[Theodore Casser]

Got a weird question about an error our customer getting in an application we wrote when it is run against Oracle 10g.

We had a Java Stored Procedure that was reading in a file and then dumping the data after parsing into Oracle 9i. The system in question has been updated to 10g and is suddenly giving us an error that the maximum number of cursors are in use. (The application was running just fine under 9i.)

We've tried telling the procedure to close and re-open the connection, but that doesn't seem to be helping.

My questions are these: Are there any changes in 10g that would have caused this? (Is anyone else having this problem?) Is there any explicit way to tell Oracle to free the cursors?

Thanks in advance!

[Maximilian Xavier Stocker]

Have you recompiled the procedures since you migrated?

Other than that I don't know. I do know that a few times I have heard of people having severe performance problems using Java Stored Procs on Oracle and these were rectified by turning them into plain vanilla Oracle stored procs instead.

It seems to me, again from experiences shared with me by several people, that Java Stored procs are a nice idea but far more trouble than they are worth. You are best to have your DBA create the procedures directly in Oracle.

[Theodore Casser]

We did try recompiling before I came here, thought maybe something else was perhaps at fault. That didn't seem to do much good, ergo why I asked.

(And no, I fully agree with you that Java Stored Procedures are more trouble than they're certainly worth. However, for the task at hand, that's what was requested. *sighs*)

I ended up talking the persons in question into letting me recode the procedure as a stand-alone application to simply prepare the data for a direct import instead. Just seemed to me to be the easiest way to handle the issue, and leave it open for bypassing these problems in the future.

Thanks for the help!