Oracle PL/SQL Best Practices

Name

DAT-10: Clean up data structures when your program terminates (successfully or with an error).

Synopsis

PL/SQL does an awful lot of cleanup for you, but there are many scenarios in which it’s absolutely crucial for you to take your own cleanup actions.

The best way to do this is to standardize on a local cleanup procedure that is to be included in each program. Call this program both at the end of the executable section and in each exception handler WHEN clause.

Example

The following program manipulates a packaged cursor, declares a DBMS_SQL cursor, and writes information to a file:

CREATE OR REPLACE 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');
   ...
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      err.log;
      RAISE;
END;

If I’m not careful, I can end up with an unclosable dynamic SQL cursor, a still-open packaged cursor that causes an “ORA-06511: PL/SQL: cursor already open” error, and a file that can’t be closed without a call to UTL_FILE.FCLOSE_ALL or a disconnect. Here’s a much better approach:

CREATE OR REPLACE 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; DBMS_SQL.CLOSE_CURSOR (dyncur); UTL_FILE.FCLOSE (fileid); END; BEGIN dyncur := DBMS_SQL.open_cursor; OPEN book_pkg.all_books_by ('FEUERSTEIN'); ...