Where should I close my cursor when an Exception arise ??
922451Mar 8 2012 — edited Mar 9 2012Hi all,
My program has many procedures and functions. Among them I have a main procedure where everything starts, and everything should ends.
My main procedure has also an exepction part with a when others clause to log any unexpected exception.
I have another procedure (let's call it P1), that is called from my main procedure, where i use an explicit cursor in a bulk collect. While i process all my cursor data, i call many others procedures and functions, and they might call many others also.
In P1 i also have an Exception part, where i have a When others clause (in case an unexpected execption arise in any other procedure or function) where I close my cursor and raise the exception to my main procedure.
My problem is that when an exception is raised to my main program (from P1), i lose the true error line number of the exception. I am using this function to get my error trace: dbms_utility.format_error_backtrace.
I did a test, and I commented the exception part in P1, so that all exceptions goes straighly to my main procedure, and the result it was that I could see the true error line again.
So my problem was the raise statement in the when others inside of P1.
The thing is that I need to close my cursor in case of any exception, but if I do it in the when others, I will lose the true error line number of the expection.
By the way I can’t declare my cursor variable as global, so that I can remove the exception part in P1, and close my cursor in my main procedure.
I will really apreciate your help.
(I am using Oracle Databas 11g).