Best Practices for PL/SQL Variables - Didn’t your parents teach you to clean up after yourself?
(Page 2 of 5 )
Clean up data structures when your program terminates (successfully or with an error).
Problem: Sometimes you really do need to clean up in a PL/SQL block.
PL/SQL does an awful lot of cleanup for you automatically, but there are a number of scenarios in which it’s absolutely crucial for you to take your own cleanup actions.
Consider the following program: it manipulates a packaged cursor, declares a DBMS_SQL cursor, and writes information to a file:
PROCEDURE busy_busy IS fileid UTL_FILE.FILE_TYPE; dyncur PLS_INTEGER; BEGIN dyncur := DBMS_SQL.OPEN_CURSOR; OPEN book_pkg.all_books_by ('FEUERSTEIN'); fileid := UTL_FILE.FOPEN ('/apps/library', 'bestsellers.txt', 'R');
... use all that good stuff in here ...
EXCEPTION WHEN OTHERS THEN err.log; RAISE; END busy_busy;
At first glance, you might want to congratulate the author for including an exception section that logs the error and then raises that exception again. Hey, at least he gave some thought to the fact that something actually could go wrong.
With a second glance, however, we uncover some drawbacks: after this program terminates (even without an error), the dynamic SQL cursor floats away, uncloseable, because the handle is erased from memory. Yet the cursor itself continues to consume SGA memory, which could cause serious problems if this kind of error is widespread. And that’s not all. The package-based cursor (all_books_by) stays open, which means that the next time this program is called, the still-open packaged cursor causes an ORA-06511: PL/SQL: cursor already open error. Wait, there’s more! The file is not closed, but the handle to the file is cleaned up, thereby making it impossible to close this file without closing all files with UTL_FILE.FCLOSE_ALL or with a disconnect.
Yuck! That program is as messy as my son’s bedroom when he was a teenager. Clearly, we need to do some cleanup. How about this?
PROCEDURE busy_busy IS fileid UTL_FILE.FILE_TYPE; dyncur PLS_INTEGER; BEGIN dyncur := DBMS_SQL.OPEN_CURSOR; OPEN book_pkg.all_books_by ('FEUERSTEIN'); fileid := UTL_FILE.FOPEN ('/apps/library', 'bestsellers.txt', 'R');
... use all that good stuff in here ...
DBMS_SQL.CLOSE_CURSOR; UTL_FILE.FCLOSE (fileid); CLOSE book_pkg.all_books_by; EXCEPTION WHEN OTHERS THEN err.log; RAISE; END busy_busy;
Now, that’s really great—as long as no error is raised. Because if the program terminates with an exception, then all the same problems occur. No problem! I will simply copy and paste those three cleanup lines into the exception section. Really? No! Terrible idea! Whenever you find yourself thinking about copying and pasting code, ask yourself: do I really want to have multiple copies of this code running around in my application?
Maybe, just maybe, it would be better to create a single program and call it wherever it is needed. I have taken that approach in my third implementation of busy_busy (below). I now have a local procedure that performs all cleanup operations. I call it at the end of the execution section (clean up on success) and in the WHEN OTHERS clause (clean up on failure).
PROCEDURE busy_busy IS fileid UTL_FILE.FILE_TYPE; dyncur PLS_INTEGER;
PROCEDURE cleanup IS BEGIN IF book_pkg.all_books_by%ISOPEN THEN CLOSE book_pkg.all_books_by; END IF;
IF DBMS_SQL.IS_OPEN (dyncur) THEN DBMS_SQL.CLOSE_CURSOR (dyncur); END IF;
IF UTL_FILE.ISOPEN (fileid) THEN UTL_FILE.FCLOSE (fileid); END IF; END cleanup; BEGIN dyncur := DBMS_SQL.OPEN_CURSOR; OPEN book_pkg.all_books_by ('FEUERSTEIN'); fileid := UTL_FILE.FOPEN ( '/apps/library', 'bestsellers.txt', 'R');
... use all that good stuff in here ...
cleanup; EXCEPTION WHEN NO_DATA_FOUND THEN err.log; cleanup; RAISE; END;
Notice that as I moved my cleanup logic into its own program, I also took the time to enhance it, so that I close only those things that are actually open. This increased attention to detail and completeness often happens quite naturally when you focus on creating a single-purpose program.
A common cleanup procedure offers several important advantages:
Your programs are less likely to have memory leaks (open cursors) and to cause problems in other programs by leaving data structures in an uncertain state.
Future developers can easily add new cleanup operations in one place and be certain that they will be run at all exit points.
When and if I add another WHEN clause, I will be very likely to follow the “model” in WHEN OTHERS and perform cleanup there as well.
Resources
The stdpkg_format.sql file on the book’s web site contains a template for a package specification and body) that you may find useful as a starting point.
