when others then null, redux
It keeps happening.
Over and over it keeps happening.
Here is case 124,215,412,523 for your review.
The user writes me:
... I have a situation where I have started receiving the 'Cursor is Closed' SQLException from a stored procedure, say, SP_A, suddenly for last 5-6 weeks, since 2nd week of March. This happens only intermittently. If I take the same parameters and execute the sp again after a few minutes after getting the error, it does not generate the error and returns expected results back.
Additional details as requested:
what ora-xxxx error are you getting
Unfortunately, it does not generate an ORA-???? error. I know that many a times, the 'Cursor is Closed' error is a coding issue, but in this case, that is not the case. I cannot create this error at will, I have not been able to create the error in our development or user acceptance environment. Even in production, I cannot recreate it with same parameters that have generated the error.
The reason I think this could be oracle memory issue is that we very recently moved from 9i to 10g RAC. This application - java code and stored procedures - have been working for about 5 to 6 years now. Even the errors of Cursor is closed we receive are not consistent i.e. The parameters that generate the error now will work with exact same code a few minutes later.
Ok, here they are - they say "there is no ora-xxxx error, Oracle is just closing cursors on us - no fair. We know this cannot be a bug in our code, this is a bug in Oracle.
The story ALWAYS goes that way. I just finally gave up... closed the question. They followed up:
What was found that SP_A had exception clause of WHEN OTHERS that was masking the actual error.
When we removed that error, this is the error we see
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SP_A"
ORA-06508: PL/SQL: could not find program unit being called: "SP_A"
ORA-06512: at "SP_B", line 317
ORA-06512: at line 1
This is what has been happening intermittently ( we just did not know, because the way the sp was
coded and the java code interacted with returned parameters, it manifested into Cursor Closed Error
and now they want "insight" into why this is happening....
Remember everyone, everyone remember, keep in mind:
When others not followed by RAISE or RAISE_APPLICATION_ERROR is almost certainly, with 99.999999999% degree of accuracy, a bug in your developed code. Just say "no" to when others not followed by raise or raise_application_error!