Dealing with errors in assigning values to packaged constants

Question:
We define constant variables in our package specifications. If there should occur an error, which exception handler would take care of that?
Answer:
The answer to this question becomes clear when you keep in mind this general rule:
An exception that is raised in the declaration or exception sections of a PL/SQL block is propagated unhandled to its enclosing block or to the host environment. The exception section of a PL/SQL block can only (possibly) handle errors raised in the executable section of that block.
Now let's apply that rule to your situation, based on the following example: CREATE OR REPLACE PACKAGE bad_constant:

IS
c_number CONSTANT NUMBER NOT NULL := 'abc';
END bad_constant;
/
When this package is initialized in my session, Oracle will raise an error, since "abc" is not a number. You can see this below:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.put_line (
3 NVL ( TO_CHAR ( bad_constant.c_number ), 'NULL!?' ));
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HR.BAD_CONSTANT", line 3
ORA-06512: at line 2
But notice that if I run it immediately a second time, I do not get the error:

SQL> BEGIN
2 DBMS_OUTPUT.put_line (
3 NVL ( TO_CHAR ( bad_constant.c_number ), 'NULL!?' ));
4 END;
5 /

NULL!?
Even though the package raised an error as it attempted to assign the default value for the variable, Oracle marked the package as initialized – resulting in a seeming contradiction: a constant defined as NOT NULL now has a NULL value.
This scenario (run it once, get an error, run it a second time and no error) can be quite bewildering to programmers, which makes it especially important that you avoid such errors. Before I give you my recommendation on how best to do that, you might be wondering if it possible to trap this error inside one's package, in the initialization section. Let's give it a try:

CREATE OR REPLACE PACKAGE BODY bad_constant
IS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Trapped error in package initialization section!' );
RAISE;
END bad_constant;
/
But after I recompile the package body and try to use the package again, I get the same error:

SQL> BEGIN
2 DBMS_OUTPUT.put_line ( bad_constant.c_number );
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HR.BAD_CONSTANT", line 3
ORA-06512: at line 1
To understand this, simply apply our rule to packages:
• The entire specification and body of a package, excepting the initialization section, are declaration sections. The specification declares what users of the package can reference. The body declares the implementation of those elements (and any desired private elements).
• The initialization section is the (optional) executable section of a package.
• The exception section at the bottom of a package will only trap errors raised in the initialization section.
Therefore, an exception raised in the package specification will be propagated unhandled out of the package, even if you include a WHEN OTHERS in your initialization section.
Now, I could conclude this column by showing you how to restructure your package so that the error becomes trappable within the initialization section. The problem with doing that is that I would violate the title of this column: PL/SQL Best Practices.
You see, the kind of error described by my fine reader is one that should be caught when the code is unit tested – and fixed by changing the code to avoid the raising of the error entirely.
For help with unit testing PL/SQL programs, I suggest you check out:
• utPLSQL, the open source unit testing framework for PL/SQL developers, available at http://utplsql.sourceforge.net/.
• Qute, the Quick Unit Test Engine, a new unit testing tool I am developing that offers a graphical interface for defining, generating and running your test code. Qute is available for downloading at www.unit-test.com, but it is not yet in production status.