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.Cleanup Needed On the PL/SQL Aisle
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.Emulating Finally
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.
DOWNLOAD Oracle Database 11g
Photography by Scott Webb, Unsplash