Oracle Built−in Packages- P85: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk WHEN OTHERS THEN OTHERS Exception sqlerrm END This is the output of the script closing in NON_EXISTENT_DIRECTORY FILECLOSE raises a VALUE_ERROR exception when passed a NULL file locator. See the FILEOPEN and FILECLOSEALL sections for other examples of FILECLOSE usage. The procedure The FILECLOSEALL procedure is used to close all BFILEs that are open within a session. Here s the header for this program PROCEDURE Exceptions The FILECLOSEALL procedure raises an UNOPENED_FILE exception if no files are open. Examples When an exception occurs after opening a file it is possible that execution continues without closing the file . a matching call to FILECLOSE is not executed due to abnormal termination of a block . In this case the file remains open and we run the risk of exceeding the SESSION_MAX_OPEN_FILES limit. It is good practice to include a call to FILECLOSEALL within an exception handler whenever FILEOPEN is used. The following anonymous block shows how you might construct an exception section that will close any open files DECLARE book_file_loc BFILE NULL book_file_exists BOOLEAN FALSE x NUMBER BEGIN book_file_loc BFILENAME BOOK_TEXT book_file_exists book_file_loc 1 IF book_file_exists THEN opening book_file_loc Intentionally raise a ZERO_DIVIDE exception x 1 0 book_file_loc END IF EXCEPTION WHEN OTHERS THEN BEGIN Clean up using FILECLOSEALL EXCEPTION WHEN THEN No files to close raising the UNOPENED_FILE exception. Working with BFILEs 411 Appendix A What s on the Companion Disk WHEN OTHERS THEN OTHERS Exception sqlerrm END END This is the output of the script opening Clean up .
