Oracle Built−in Packages- P7: 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 SQLERRM returns the standard Oracle error message NOTE You will not be able to write a statement like WHEN in the Oracle Developer 2000 Release 1 environment. See Section Calling Built-in Packaged Code from Oracle Developer 2000 Release 1 for more information on this restriction. Package-defined exception In this scenario the package declares one or more exceptions by name only these exceptions do not have message text or a unique number associated with them. When this exception has been raised SQLCODE will always return 1 and SQLERRM will always return the Unhandled user-defined exception message. As a consequence you have two basic options for handling these exceptions You handle by name write an exception handler that references the packaged exception by name. You rely on the WHEN OTHERS clause in which case there is no way for you to know precisely which exception was raised. Let s look at the UTL_FILE package for an example. The following exceptions are defined in the package specification PACKAGE UTL_FILE IS invalid_path EXCEPTION invalid_mode EXCEPTION invalid_filehandle EXCEPTION invalid_operation EXCEPTION read_error EXCEPTION write_error EXCEPTION internal_error EXCEPTION END The function can raise the INVALID_MODE INVALID_OPERATION or INVALID_PATH exceptions. I can write an exception section for a program using in one of two ways PROCEDURE my_program IS fid BEGIN fid tmp R EXCEPTION WHEN THEN WHEN THEN WHEN THEN END or PROCEDURE my_program Exception Handling and Built-in Packages 21 Appendix A What s on the Companion Disk IS fid BEGIN fid tmp R . EXCEPTION WHEN OTHERS Not recommended Information is lost. THEN . END When working with this kind of exception always use the first .
