my dog learned polymorphism
The moose likes JDBC and Relational Databases and the fly likes Explicitly Freeing Cursors in 10g? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Explicitly Freeing Cursors in 10g?" Watch "Explicitly Freeing Cursors in 10g?" New topic

Explicitly Freeing Cursors in 10g?

Theodore Casser
Ranch Hand

Joined: Mar 14, 2001
Posts: 1902

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!

Theodore Jonathan Casser
SCJP/SCSNI/SCBCD/SCWCD/SCDJWS/SCMAD/SCEA/MCTS/MCPD... and so many more letters than you can shake a stick at!
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
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
Ranch Hand

Joined: Mar 14, 2001
Posts: 1902

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!
I agree. Here's the link:
subject: Explicitly Freeing Cursors in 10g?
It's not a secret anymore!