Re: jdbc connection/statements

From: Bastiaan Schaap <fake_address_at_nomail.com>
Date: Fri, 22 Jun 2001 07:49:04 +0200
Message-ID: <u0BY6.1$fY.337@psinet-eu-nl>

Hi Sudarshan,

Although I don't have experience with connecting to Oracle from Java, the ORA error message tells more than enough.... The 'problem' is that every statement object opens a cursor in the database. Within this cursor your statement will be executed in the database. When a database is created, a maximum number of open cursors is set by the DBA in the init<sid>.ora file.

As far as I can see (I'm not really a Java programmer), within your code if an exception gets raised, the cursor isn't closed. So for every exception a cursor will remain open (in that session), and if it happens enough times you will hit the maximum open cursors defined in the init file. Also if you have recursive code, it can be that a lot of cursors are opened at the same time, which could also result in hitting the max open cursors. So what you should try to do is:

make sure that open cursors are closed whenever an exception occurs
try to close every cursor as soon as you have the result from the statements that were executed in that cursor

In your case closing a cursor would probably be destroying the object I guess.... If you have optimized the code in this way, and you keep hitting the maximum number of open cursors, you can set the parameter that controls this number to an increased value. This will no-doubt have some impact on your database, but I'm sure that Sybrand or one of the other guru's will be happy to comment on this (I'm also not a DBA ;-) ).

HTH, Bastiaan Schaap

"Time is the best teacher; Unfortunately it kills all its students!" Received on Fri Jun 22 2001 - 00:49:04 CDT