Home » SQL & PL/SQL » SQL & PL/SQL » stored proc > cursor as output to java > max cursors exceeded > due to not closing in excep
stored proc > cursor as output to java > max cursors exceeded > due to not closing in excep [message #187201] Fri, 11 August 2006 05:03 Go to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
hello.

we are working already some years with stored procedures in a pacakge, where some stored procs give a cursor as output. we mostly call that stored procedure with some java code.

until so far it works fine for 2 years, but now we have
a problem "maximum cursors exceeded".

we started investigating the stored procs and found out that our cursors are never closed when they have to serve as output... i think this is normal, right?

but is it possible that for some reason the stored procedure falls in exception, and the cursor is not closed automatically? can this be the reason that we have this error now?
is it normal we sould close them manually in the exception blocK?

thanks for help !
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187285 is a reply to message #187201] Fri, 11 August 2006 12:29 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You need to close those cursors at the Java layer after you are done fetching.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187303 is a reply to message #187201] Fri, 11 August 2006 14:28 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
and what when the function in the stored proc falls in exception after the cursor is opened?
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187396 is a reply to message #187303] Sun, 13 August 2006 07:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then (try to) close them in the Exception block.

somewhing like

try {
 .....
}
catch (Exception e) {
    // Handle exception 
    e.printStackTrace();
    
    // Try to close everything that might have been opened
    try {
        results.close();
        cstmt.close();
    } 
    catch (Exception e) { /* do Nothing */ }
}
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187400 is a reply to message #187201] Sun, 13 August 2006 10:20 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
i mean when the stored procedure itself falls in error (not the java code).

i know, if it is like that i can close it also in the exception block of the stored proc, but my question is: is this normal that you have to it manually in the exceptions ?
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187470 is a reply to message #187201] Mon, 14 August 2006 02:48 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
Could you provide a small example? It will be more informative.

2 ThomasG.
reasonable update.
try {
 .....
} catch (SQLException ex) {
    // exception handling here
} finally {
    // free resources here
    try {
        if (results != null) results.close();
    } 
    catch (SQLException e) { /* do Nothing; or log it */ }
    try {
        if (cstmt != null) cstmt.close();
    } 
    catch (SQLException e) { /* do Nothing; or log it */ }
}
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187600 is a reply to message #187400] Mon, 14 August 2006 11:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Normally, there would not be no or very little code following the 'open' in the proc, so there would be very little chance for an exception to be raised.

If you do have code following the open, I would first ask if it really has to be after the open. If so, then you will need a close in the exception handling of the proc.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187619 is a reply to message #187201] Mon, 14 August 2006 13:21 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
@orafaqer: it is impossible to post here the java code that handles the recordset because the code is not visible java, but it is webmethods (based on java). so we work with standard packages and functionality.

@Todd Bary: it crashes because of a corrupt varchar in the table that is converted to a date as output, in a select statement from the cursor.

So is it the responsiblity of the programmer to close the cursor in the exception block? or hasn't the oracle database some functionality to auto-close open cursors, or at the end of the stored proc or something, or when it falls in exception ?
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187620 is a reply to message #187201] Mon, 14 August 2006 13:23 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
if an experienced oracle guy here can confirm this is the responibility of the programmer, to close cursors in the exception block of the stored proc, then i think this is a good warning for other newbee programmers !!! I've never seen anyone doing this, but now we have big big problems in our production environment because of this.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187638 is a reply to message #187620] Mon, 14 August 2006 15:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If the exception is caused by the select statement, the exception will not be raised until the Java layer trys the fetch. So, in this case, exception handling in the proc that closes the cursor is irrelevant.

The Java exception handling needs to close it.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187704 is a reply to message #187201] Tue, 15 August 2006 01:13 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
i can follow your way, but if we close the cursor in the exception block of the stored proc, it works.

closing the cursor at the java layer is a bit difficult because we work with webmethods; packages with basic java functionality that eases development and increases development speed... and there's no "close cursor package" Sad
i does works when we disable and enable the jdbc connection.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #187710 is a reply to message #187704] Tue, 15 August 2006 01:51 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
r2d2dev wrote on Tue, 15 August 2006 10:13

@orafaqer: it is impossible to post here the java code that handles the recordset because the code is not visible java, but it is webmethods (based on java). so we work with standard packages and functionality.

(It isn't significant where java code is running and what are you using. You are always able to provide test case)
Ok. Show PL/SQL code, please. I hope this could be done.
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #188409 is a reply to message #187201] Fri, 18 August 2006 07:38 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
well, this is the code of the stored proc:

CREATE OR REPLACE PACKAGE BODY TEST_2
AS

PROCEDURE test_cursor_2 (
      p_recordset          OUT   cursor_type,
      return_code_out      OUT   VARCHAR2,
      return_message_out   OUT   VARCHAR2
   )
   IS


   BEGIN
      return_code_out := '00000';
      return_message_out := 'OK';
	  


      OPEN p_recordset FOR

             select col1, col2
             from table1
             where SYSDATE > TO_DATE (servicedate, 'DD-MM-YYYY HH24:MI:SS');

	  	  	  
   
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         return_code_out := '43851';
         return_message_out := 'Error: Could not find required data.';

		 
      WHEN TOO_MANY_ROWS
      THEN
         return_code_out := '43811';
         return_message_out := 'Error: Too many rows selected.';

      WHEN OTHERS
      THEN
         return_code_out := '43899';
         return_message_out := 'Unknown error.';
	 
   END test_cursor_2;   
   
END TEST_2;
/
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #188410 is a reply to message #187201] Fri, 18 August 2006 07:42 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
now i found out something else.
when i reached the point where we get the opencurors exceeded error, all subsequent calls to the stored procedure package fail. but another application can still call the stored procedure !!!

is the behaviour above normal ?

so, the maximum number of cursors in the database, is it related to one session, connection or to the whole database?
Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #188413 is a reply to message #187201] Fri, 18 August 2006 08:03 Go to previous messageGo to next message
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
Member
and also, i can catch the no_data_found exception, but it seems when the "maximum open cursors" exception occurs, it doesn't falls in the OTHERS-block of my exception block ?

Re: stored proc > cursor as output to java > max cursors exceeded > due to not closing in e [message #188453 is a reply to message #188409] Fri, 18 August 2006 11:14 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You are never to going to hit any of those named exceptions. An OPEN never generates an exception if there is no matching data. You would only hit no_data_found or too_many_rows if you were using a SELECT INTO statement.

Trying to pass error codes back through out parameters really defeats the whole purpose of exception handling. If an exception is ever raised here, and the chances of that happening are incredibly slim based on the supplied code, just let the exception naturally be raised up.

This proc should really be nothing more than:

procedure test_cursor_2(
   p_recordset out cursor_type
)
is
begin
   open p_recordset for
      select col1, col2
        from table1
       where sysdate > to_date(servicedate, 'DD-MM-YYYY HH24:MI:SS');
end test_cursor_2;
Previous Topic: stored proc > error doesn't fall in the exception block
Next Topic: Date Question
Goto Forum:
  


Current Time: Sat Dec 10 20:33:33 CST 2016

Total time taken to generate the page: 0.24657 seconds