SOA 11g: ORA-00904, ORA-00942 and PLS-00201 SQL Errors Encountered While Running soa_purge_scripts.sql to Install SOA Purge Scripts (Doc ID 1301211.1)

Last updated on NOVEMBER 18, 2016

Applies to:

Oracle SOA Platform - Version 11.1.1.3.0 and later
Information in this document applies to any platform.

Symptoms

You have an installation of Oracle Fusion Middleware 11g Release 1 Patchset 3 (11.1.1.4.0) and are installing the SOA purge scripts supplied within the product distribution which are located at:

$MW_HOME/$ORACLE_SOA/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql


For example:

/u01/Middleware/Oracle_SOA1/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql


When you run this SQL script you experience a number of SQL errors that cause the script to fail, as shown below:

$ sqlplus dev_soainfra/******

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 3 10:06:56 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @soa_purge_scripts.sql
Procedure created.
Procedure created.
Procedure created.
Procedure created.
Function created.
Function created.
Function created.
Function created.
Procedure created.
Function created.
Procedure created.
Procedure created.
Type created.
Type body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.
PL/SQL procedure successfully completed.
Package created.

Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY SOA_FABRIC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
354/11 PL/SQL: SQL Statement ignored
354/85 PL/SQL: ORA-00904: "ECID": invalid identifier
359/11 PL/SQL: SQL Statement ignored
359/44 PL/SQL: ORA-00904: "ECID": invalid identifier
PL/SQL procedure successfully completed.

Package created.
Package body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.
Package created.

Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY SOA_B2B:
LINE/COL ERROR
------- -----------------------------------------------------------------
96/6 PL/SQL: SQL Statement ignored
97/11 PL/SQL: ORA-00942: table or view does not exist
98/3 PL/SQL: Item ignored
98/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.BM_MESSAGE_ID' must be declared
100/3 PL/SQL: Item ignored
100/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.BM_PAYLOAD_STORAGE' must be declared
102/3 PL/SQL: Item ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
102/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.BM_ATTACHMENT_STORAGE' must be declared
104/3 PL/SQL: Item ignored
104/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.BM_NATIVE_DATA_STORAGE' must be declared
106/3 PL/SQL: Item ignored
106/31 PLS-00201: identifier
'B2B_PURGE_TEMP_BM.BM_TRANSLATED_DATA_STORAGE' must be declared

LINE/COL ERROR
-------- -----------------------------------------------------------------
108/3 PL/SQL: Item ignored
108/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.WM_PACKED_MESSAGE' must be declared
110/3 PL/SQL: Item ignored
110/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.WM_PAYLOAD_STORAGE' must be declared
112/3 PL/SQL: Item ignored
112/30 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.AM_PAYLOAD' must be declared

LINE/COL ERROR
-------- -----------------------------------------------------------------
114/3 PL/SQL: Item ignored
114/31 PLS-00201: identifier 'B2B_PURGE_TEMP_BM.AM_ATTACHMENT_STORAGE' must be declared

PL/SQL procedure successfully completed.
Package created.
Package body created.
SQL>

Changes

You may have recently migrated the affected Oracle Fusion Middleware installation from version 11.1.1.3.0 to 11.1.1.4.0.

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