How to compile INVALID REPLICATION Objects (Doc ID 118416.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 to 11.1.0.7 [Release 8.0.3 to 11.1]
Information in this document applies to any platform.
*** Checked for relevance on 22-Dec-2013 ***


Goal

You have some Replication packages and a view that have a status of ‘INVALID’ in DBA_OBJECTS. Objects getting invalidated is more common during upgradation

Even after running catproc.sql and catrep.sql the packages are still invalid

1* ALTER PACKAGE DBMS_DEFER_INTERNAL_SYS COMPILE BODY
DEVW>/

Warning: Package Body altered with compilation errors.

DEVW>SHOW ERRORS
Errors for PACKAGE BODY DBMS_DEFER_INTERNAL_SYS:

LINE/COL         ERROR
----------------------------------------------------------------------------
65/10  PL/SQL: SQL Statement ignored
65/39  PLS-00417: unable to resolve "STEP_NO" as a column

161/5  PL/SQL: SQL Statement ignored
161/35 PLS-00390: undefined column 'ENQ_TID' in INSERT statement

DEVW>ALTER PACKAGE DBMS_DEFER_QUERY_UTL COMPILE BODY;

Warning: Package Body altered with compilation errors.

DEVW>SHOW ERRORS
Errors for PACKAGE BODY DBMS_DEFER_QUERY_UTL:
LINE/COL  ERROR
----------------------------------------------------------------------------
152/7     PL/SQL: SQL Statement ignored
154/19    PLS-00414: no column 'STEP_NO' in table
187/7     PL/SQL: SQL Statement ignored
189/19    PLS-00414: no column 'STEP_NO' in table

DEVW>ALTER PACKAGE DBMS_DEFER_SYS COMPILE BODY;

Warning: Package Body altered with compilation errors.

DEVW>SHOW ERRORS
Errors for PACKAGE BODY DBMS_DEFER_SYS:
LINE/COL           ERROR
----------------------------------------------------------------------------
83/7     PL/SQL: SQL Statement ignored
83/14    PLS-00201: identifier 'LAST_SEQ' must be declared
157/9    PL/SQL: SQL Statement ignored
165/41   PLS-00414: no column 'LAST_ENQ_TID' in table
207/9    PL/SQL: Statement ignored
207/12   PLS-00364: loop index variable 'CUR_TXN' use is invalid
214/9    PL/SQL: SQL Statement ignored
214/57   PLS-00364: loop index variable 'CUR_TXN' use is invalid
218/9    PL/SQL: SQL Statement ignored
218/56   PLS-00364: loop index variable 'CUR_TXN' use is invalid
398/10   PLS-00341: declaration of cursor 'DC' is incomplete or malformed
399/5    PL/SQL: SQL Statement ignored
402/16   PLS-00414: no column 'ENQ_TID' in table
407/8    PL/SQL: Item ignored
510/7    PL/SQL: Statement ignored
510/48   PLS-00364: loop index variable 'CR' use is invalid
511/7    PL/SQL: Statement ignored
511/48   PLS-00364: loop index variable 'CR' use is invalid
513/7    PL/SQL: Statement ignored
513/54   PLS-00364: loop index variable 'CR' use is invalid

DEVW>ALTER PACKAGE DBMS_DEFER_SYS_PART1 COMPILE BODY;

Warning: Package Body altered with compilation errors.

DEVW>SHOW ERRORS
Errors for PACKAGE BODY DBMS_DEFER_SYS_PART1:
LINE/COL         ERROR
----------------------------------------------------------------------------
26/7    PL/SQL: SQL Statement ignored
27/18   PLS-00414: no column 'ENQ_TID' in table
49/3    PL/SQL: SQL Statement ignored
50/6    PLS-00390: undefined column 'ENQ_TID' in INSERT statement
310/7   PL/SQL: SQL Statement ignored
312/16  PLS-00414: no column 'ENQ_TID' in table
341/5   PL/SQL: SQL Statement ignored
342/11  PLS-00417: unable to resolve "STEP_NO" as a column
361/5   PL/SQL: SQL Statement ignored
363/11  PLS-00417: unable to resolve "LAST_ERROR_NUMBER" as a column
369/9   PL/SQL: SQL Statement ignored
370/45  PLS-00390: undefined column 'LAST_ERROR_NUMBER' in INSERT statement

DEVW>ALTER PACKAGE DBMS_REPCAT_VALIDATE COMPILE BODY;

Warning: Package Body altered with compilation errors.

DEVW>SHOW ERRORS
Errors for PACKAGE BODY DBMS_REPCAT_VALIDATE:
LINE/COL   ERROR
----------------------------------------------------------------------------
347/8   PL/SQL: SQL Statement ignored
348/29  PLS-00201: identifier 'DEFSCHEDULE' must be declared
356/7   PL/SQL: Statement ignored
357/43  PLS-00364: loop index variable 'DATABASELINK' use is invalid

DEVW>ALTER VIEW DEF$_TRAN COMPILE;

Warning: View altered with compilation errors.

Solution

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