Error ORA-04030 : out of process memory when trying to allocate 4108 bytes when compiling package

Applies to:

Oracle Payables - Version: 12.0.1
This problem can occur on any platform.

Symptoms

In  Release 12 instances , the package XLA_00200_AAD_S_000010_PKG fails to compile by firing the Error ORA-04030 : out of process memory when trying to allocate 4108 bytes.

STEPS
The issue can be reproduced at will with the following steps:
1. Change database initialization parameters.
2. Convert pl/sql code type to native following steps under
"Modifying the Entire Database for PL/SQL Native or Interpreted Compilation (page 11-29)"
of "Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2)".
3. Recompile package body XLA_00200_AAD_S_000010_PKG.

Changes

Changed the database initialization parameter plsql_code_type to 'native' as per Note 396009.1 Database Initialization Parameters for Oracle Applications Release 12 and converted the pl/sql code to native as per Oracle Database PL/SQL User's Guide and Reference.

Cause

The issue is reported in the internal unpublished Bug which interprets the reason of the failure as follows:

Package size is more than 130K lines which make the sql optimization use too much memory.   The system generated packages are not shipped with the product and they depend on the customer setup so packages can vary in size from one situation to another.

Solution

Action Plan:

1.Set the plsql_optimize_level to 0 before compiling the Package by doing the following:

SQL>alter system set plsql_optimize_level =0;

2.Run the alter command to compile the package

3.Set the plsql_optimize_level to 2 after compiling the package as recommended per the following reference Note 396009.1 (Database Initialization Parameters for Oracle Applications Release
12), plsql_optimize_level should be set to '2'

Note :

• Please note that if the plsql_optimize_level is set '0' after the package compilation , this will affect the performance of the application as  it prevents the code from being rearranged at all. 
  
• The Difference between '0' and '2' for plsql_optimize_level.is as follows:
  1. Level 0
     
     Maintains the evaluation order and hence the pattern of side effects, exceptions, and package
     initializations of Oracle9i and earlier releases. Also removes the new semantic identity of
     BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g.
     
  2. Level 2
     
     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.
     Generally, setting this parameter to 2 pays off in better execution performance. If, however, the compiler runs slowly on a particular source module or if optimization does not make sense for some reason (for example, during rapid turnaround development), then setting this parameter to 1 will result in almost as good a compilation with less use of compile-time resources.