Invalid Objects (Errors PLS-00201 and PLS-00320) Caused by UTL_FILE Permissions (Doc ID 432961.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle OLAP - Version 10.2.0.4 and later
Oracle Data Mining - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Several objects in (for example) the OLAPSYS and DMSYS schemas are flagged as invalid, as shown below:

SQL>select object_name
from dba_objects
where status <> 'VALID' and owner IN ('OLAPSYS','DMSYS');

The output reports the following objects:

CWM2_OLAP_MANAGER
CWM2_OLAP_UTILITY
CWM2_OLAP_EXPORT
CWM2_OLAP_PC_TRANSFORM
CWM2_OLAP_AW_AWUTIL
OLAPFACTVIEW
OLAPDIMVIEW
DBMS_ODM
CWM2_OLAP_OLAPAPI_ENABLE

Try to recompile a package manually to see if this is the issue at hand:

SQL>alter package olapsys.dbms_odm compile body;
Warning: Package Body altered with compilation errors.

SQL>show errors
PLS-00201: identifier 'UTL_FILE' must be declared
PLS-00320: the declaration of the type of expression is incomplete or malformed

as well as Statement ignored and Item ignored entries.