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.
Next: Programmers are (or should be) control freaks >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Check it out today at your favorite bookstore. Buy this book now.
|
|
