Package body is INVALID after impdp (Doc ID 1313767.1)

Last updated on OCTOBER 26, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.

Symptoms

Package body's that are VALID in the SOURCE database are INVALID in the target database upon import using impdp:

SQL> select object_name,object_type,owner from dba_objects where status='INVALID';

OBJECT_NAME                  OBJECT_TYPE  OWNER
---------------------------- ------------ ------
XLA_00140_AAD_S_000018_PKG   PACKAGE BODY APPS
XLA_00707_AAD_S_000018_PKG   PACKAGE BODY APPS
XLA_00707_AAD_S_000019_PKG   PACKAGE BODY APPS


Attempts to manually compile each package body fail with the same PLS-00103 error:

SQL> alter package XLA_00140_AAD_S_000018_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY XLA_00140_AAD_S_000018_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
131073/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         ( begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-identifier>
         <a bind variable> << continue close current delete fetch lock
         insert open rollback savepoint set sql execute commit forall
         merge pipe purge


A query on the SOURCE database against DBA_SOURCE shows that these package body's have more than 262,144 lines:

SQL> select name, max(line) from dba_source
  2  where owner = 'APPS'
  3    and type = 'PACKAGE BODY' group by name;

NAME                           MAX(LINE)
------------------------------ ----------
...
XLA_00140_AAD_S_000018_PKG     281134
XLA_00707_AAD_S_000018_PKG     437833
XLA_00707_AAD_S_000019_PKG     437833
...

Changes

The package body's are truncated by impdp.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms