On Emulating FINALLY

Code PL/SQL to function like the Java FINALLY section.

January/February 2009

I just moved over from the Java world to PL/SQL. One thing I really miss from Java is the FINALLY section of a method. How can I get the same behavior out of PL/SQL?

Unlike Java, PL/SQL does not support a FINALLY section. You can, however, emulate much of what this section does, through careful and disciplined use of local subprograms.

First, I’ll look at how FINALLY works in Java, then I’ll explore why it would be useful in PL/SQL, and last I’ll show how to emulate it.

In Java, the FINALLY section always executes when the TRY section of a method exits—even if an unexpected exception has been raised. The FINALLY section ensures that cleanup logic is not bypassed or ignored, regardless of where or how a program terminates. The programmer does not have to specifically invoke this section or call the code in it. The Java engine automatically runs it before returning control from a method.

In the PL/SQL world, there are several actions that require explicit cleanup statements, including the following:

Opening a file by using UTL_FILE.FOPEN. I must then close the file by using UTL_FILE.FCLOSE; otherwise, the file will remain open until my connection is terminated or until I call UTL_FILE.FCLOSE_ALL to close all the files opened in my session.

Opening a cursor by using DBMS_SQL.OPEN_CURSOR. I must close the cursor by using DBMS_SQL.CLOSE_CURSOR, or that cursor will remain open until my connection is terminated.

Allocating memory for package-level variables. Variables declared at the package level retain their values (and the memory allocated for those values) for the duration of my session, even if the block in which the value was assigned has been closed. If I don’t want that memory to continue to store the variable values, I must explicitly release that memory.

Let’s take a look at a program that works with files and dynamic SQL—and the problems that can arise when you don’t clean up properly after yourself. I will use the typical and ubiquitous quick-and-dirty methodology to throw together a program (exec_sql_from_file) that reads a file and executes its contents as a single SQL statement using DBMS_SQL. I’m assuming that this is a method one dynamic SQL statement (DDL or DML and without any bind variables).

Here is an explanation of the exec_sql_from_file procedure in Listing 1:

Code Listing 1: exec_sql_from_file (before FINALLY emulation)

  1  PROCEDURE exec_sql_from_file (
  2         dir_in    IN     VARCHAR2
  3       , file_in   IN     VARCHAR2
  4  )
  5  IS
  6     l_file         UTL_FILE.file_type;
  7     l_lines       DBMS_SQL.varchar2a;
  8     l_cur         PLS_INTEGER;
  9     l_exec       PLS_INTEGER;
 10  BEGIN
 11     BEGIN
 12        l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
 13
 14        LOOP
 15           UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
 16        END LOOP;
 17     EXCEPTION
 18        WHEN NO_DATA_FOUND
 19        THEN
 20             /* All done reading the file. */
 21             NULL;
 22     END;
 23
 24     l_cur := DBMS_SQL.open_cursor;
 25     DBMS_SQL.parse (l_cur
 26                            , l_lines
 27                            , l_lines.FIRST
 28                           , l_lines.LAST
 29                            , TRUE
 30                           , DBMS_SQL.native
 31                             );
 32     l_exec := DBMS_SQL.EXECUTE (l_cur);
 33 END exec_sql_from_file;

Lines 12-22. Use UTL_FILE to open the specified file, and read its contents into an array, which is declared by use of a DBMS_SQL type.

Lines 18-21. When UTL_FILE.GET_LINE reads past the end of the file, it raises the NO_DATA_FOUND exception. It traps that exception and then uses the NULL statement to tell the program to continue.

Lines 24-32. Use the overloading of DBMS_SQL.PARSE (which accepts an array of strings) to parse the entire contents of the file, and then execute the cursor. These lines perform the dynamic SQL operation. This use of dynamic SQL and array overloading will work in all releases of Oracle Database, but note that in Oracle Database 11g, both DBMS_SQL.PARSE and EXECUTE IMMEDIATE accept a CLOB, so you will no longer have to use this array overloading for very large (greater than 32K) SQL statements.

So in PL/SQL I needed just 33 lines of code to implement a procedure that reads the contents of a file and executes it as a SQL statement. Unfortunately, this is very messy code. I have neglected to implement the cleanup steps: close the file and close the cursor. As a result, the file remains open for the duration of my session (or until I call UTL_FILE.FCLOSE_ALL). The cursor also remains open until I disconnect.

Now I will show you how to emulate as closely as possible the behavior of a FINALLY clause in PL/SQL, using a locally defined cleanup subprogram.

To ensure that I clean up properly and close any open resources, I need to add two lines before the end of my procedure (between lines 32 and 33 in Listing 1):

UTL_FILE.fclose (l_file);
DBMS_SQL.close_cursor (l_cur);

Am I done, then? Only if I never have any problems running this program.

In the real world, every possible thing that could go wrong does go wrong. So in the case of exec_sql_from_file, I need to add an exception handler to trap errors, log error information as specified by my application standards, clean up from the effects of the program, and raise the exception again.

The following adds the previously mentioned cleanup logic and an exception section before the end of the exec_sql_from_file procedure (between lines 32 and 33 in Listing 1):

   UTL_FILE.fclose (l_file);
   DBMS_SQL.close_cursor (l_cur);
EXCEPTION
   WHEN OTHERS
   THEN
      log_error ();
      UTL_FILE.fclose (l_file);
      DBMS_SQL.close_cursor (l_cur);
   RAISE;

I now have a fairly robust procedure that cleans up after itself, whether it ends successfully or with an error. But I would rather not duplicate the cleanup logic in multiple places.

Furthermore, my exception section code assumes that both the file and the cursor have been opened. If I encounter a problem in reading the file, I will never even get to the dynamic SQL portion of my program (lines 24 through 32). Thus, I might try to close a cursor that is not open and raise an exception. The error that will be raised depends on the version of Oracle Database. (If I am running Oracle Database 11g, this action will disable the use of DBMS_SQL entirely for my session, requiring me to reconnect.)

I really should close my resource only if it is open, and that further complicates the cleanup code I need to write. I could simply add that code to the exception section, but what if I need to trap a specific exception? I will need to clean up there too and duplicate even more code. My program will be much more elegant and easy to maintain if I can consolidate all my cleanup logic into one reusable subprogram.

So I implement a small local sub-program inside exec_sql_from_file that performs all my cleanup operations:

PROCEDURE exec_sql_from_file (
   dir_in    IN   VARCHAR2
 , file_in   IN   VARCHAR2
)
IS

... declarations as before ...
   PROCEDURE cleanup
   IS
   BEGIN
      IF SQLCODE <> 0
      THEN
         log_error ();
      END IF;
      IF UTL_FILE.is_open (l_file)
      THEN
         UTL_FILE.fclose (l_file);
      END IF;
      IF DBMS_SQL.is_open (l_cur)
      THEN
         DBMS_SQL.close_cursor (l_cur);
      END IF;
   END cleanup;

I call this cleanup program at both exit points of my exec_sql_from_file procedure: successful completion (the end of my executable section) and the occurrence of any error (in the WHEN OTHERS clause). The following code assumes that the cleanup procedure has been added to the exec_sql_from_file procedure and replaces the last line of exec_sql_from_file in Listing 1 with

   cleanup ();
EXCEPTION
   WHEN OTHERS
   THEN
      cleanup ();
      RAISE;
END exec_sql_from_file;

Listing 2 shows the revised exec_sql_from_file procedure, with FINALLY emulation.

Code Listing 2: exec_sql_from_file (with finally emulation)

PROCEDURE exec_sql_from_file (
   dir_in    IN   VARCHAR2
 , file_in   IN   VARCHAR2
  )
  IS
     l_file    UTL_FILE.file_type;
     l_lines   DBMS_SQL.varchar2a;
     l_cur     PLS_INTEGER;
     l_exec    PLS_INTEGER;
PROCEDURE cleanup
IS
BEGIN
   IF SQLCODE <> 0
   THEN
      log_error ();
   END IF;
   IF UTL_FILE.is_open (l_file)
   THEN
      UTL_FILE.fclose (l_file);
   END IF;
   IF DBMS_SQL.is_open (l_cur)
   THEN
       DBMS_SQL.close_cursor (l_cur);
   END IF;
END cleanup;
BEGIN
    l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
    LOOP
       UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
    END LOOP;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
         /* All done reading the file. */
         NULL;
END;
BEGIN
   l_cur := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (l_cur
                       , l_lines
                       , l_lines.FIRST
                       , l_lines.LAST
                       , TRUE
                       , DBMS_SQL.native
                         );
   l_exec := DBMS_SQL.EXECUTE (l_cur);
cleanup ();
EXCEPTION
    WHEN OTHERS
    THEN
         cleanup ();
         RAISE;
END exec_sql_from_file;

This approach of segregating all cleanup logic into a single subprogram and then calling it at the end of the executable section and in each exception handler is the closest you can come to emulating the Java FINALLY clause in PL/SQL.