This is the typical example when compiler during the compilation of the source code, can introduce bugs, as Oracle has to re-arrange the code and make many modifications (optimizations like inline expansion etc.) of the code to achieve better performance.
With higher level of optimization, compiler modifications will make will be more intrusive.
The same situation is with C/C++ compilers, that by setting appropriate flags, will try to get the maximum performance from the code.
In alert log (and adrci report as well) I can found many occurrences of ORA 7445 [pevm_MOVC_i()+112].
By looking at the trace file, I can find the following:
... Current SQL Statement for this session (sql_id=dbyj2sxkrsz3k) ----- BEGIN DF33_ASNOUT_SQL.CONSUME_ASNOUT(O_ERROR_TYPE=>:1 , O_STATUS_CODE=>:2 , O_ERROR_MESSAGE=>:3 , I_MESSAGE=>:4 , I_MESSAGE_TYPE=>:5 , I_MESSAGE_TSF=>:6 , I_MESSAGE_TYPE_TSF=>: 7 ); END; ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 7000005f6b5cc68 138 package body RMS.RMSSUB_ASNOUT 700000413d914e8 817 package body KIP.DF33_ASNOUT_SQL 7000007aa251148 1 anonymous block ...
Basically, the package, in most cases, is running fine. There are no syntax or functional errors.
For that reason, I conclude it must be something related to the package DF33_ASNOUT_SQL, and based on my previous experience, I decided to try to play with PLSQL_OPTIMIZE_LEVEL.
From the Oracle Reference document, this is the description of the PLSQL_OPTIMIZE_LEVEL:
PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Default value is 2, where the value 0 means no PL/SQL optimization at all.
Below are the most important part related to level 0, 1 & 2 from the Oracle documentation.
Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases.
Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions.
Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.
After recompilation of the DF33_ASNOUT_SQL package with plsql_optimize_level 0, the problem has been resolved.
alter package kip.df33_asnout_sql compile plsql_optimize_level=0;
My suggestion when facing the similar ORA 7445 error is to first check the trace file to check if
the same code is involved.
If yes, try to recompile the procedure/fumction/package with plsql_optimize_level=0 and check if that could solve the problem.