Avoiding ORA-04068

This is a nasty error that can occur when a package is recompiled during an Oracle session. I work now in an environment where this occasionally happens and sometimes minutes of data entry are lost.

The same statement will execute without problem when re-issued but for this the software needs to be  thoroughly revised. 

The ORA-04068 problem is explained very well in a number of posts like Avoiding ORA-04068: existing state of packages has been discarded. One of the solutions is avoiding state fullness of packages, i.e. no global constants or variables. When inspecting the packages in the system I found that quite a number of packages only had global constants and no variables. At first I thought about moving the constants to a separate package. Then only changes to this package would cause a ORA-04068. The drawback of this solution is that code throughout the packages would have to be modified. 

Another solution is to replace the constants by functions. This way there is no global state and no code to be changed when the functions get the same name as the constants. This also works for public constants. So

 
 LF      VARCHAR2(10) := CHR(10);

is changed to

 
  FUNCTION LF RETURNS VARCHAR2 IS
  BEGIN
    RETURN(CHR(10));
  END;

To make things even easier I have coded a conversion utility where you can input your constant declarations a retrieve the corresponding functions. You find it here.

Enjoy!