Votes

Add a FINALLY clause to PL/SQL blocks (details)
 
Dear Bryn,

I love PL/SQL and really appreciate the clean, understandable block structure.

It would, however, be REALLY nice if you could add a FINALLY section so I could easily and comprehensively clean up when my program terminates.

Thanks so much!


PL/SQL does not have a FINALLY clause, as you will find in many other languages. The code in this clause executes, no matter how the subprogram terminates (successfully or through an exception section. Because PL/SQL lacks FINALLY, if you have cleanup work to do when your program terminates, you have to make sure to include that code at the end of the executable section and also in every WHEN clause.


It would be very useful to have a section like the "finally" construction in Java. So you'd have a block of the form

BEGIN
...
EXCEPTION
...
FINALLY
...
END

where the code in the final section is always executed, regardless of how the block is exited. Often the EXCEPTION section would not be needed, as currently it is necessary to handle an exception just to do some clean up and then re-raise it. (Idea first brought to my attention by Chris Rimmer)


BEGIN
   ... my code

   ... clean up 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ... clean up 
      ... handle NDF
   WHEN OTHERS THEN
      ... clean up 
      ... log error
END;


BEGIN
   ... my code
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ... handle NDF
   WHEN OTHERS THEN
      ... log error
FINALLY
   ... clean up
END;


Steven Feuerstein, PL/SQL Evangelist, Quest Software: "Help me help Oracle improve the PL/SQL language!"


Bryn Llewellyn: "We love to hear from PL/SQL developers. Let us know what is important to you!"

Links

PL/SQL Obsession
Apex Evangelists
O'Reilly Books on Oracle
OTN PL/SQL Best Practices
OTN PL/SQL Page
Steven Feuerstein's Blog