Oracle Application Express component is INVALID

I have created a new Database in Oracle 11gR2 and process is smooth without any errors. When I check the status of components, it showing that Oracle application Express is INVALID.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

Possible Cause:-
1. If one or more Application Express objects are invalid
2. When doing export/import privileges might have lost
3. The XDB or Oracle Text components are not installed or de-installed.

To find the cause run the APEX Installation Verification Script apex_verify.sql and this script will generate an output apex_verify_out.html. You can download the verification script from below metalink note.

APEX Installation Verification Script [ID 1254932.1]

From the apex_verify_out.html output I found that WWV_FLOW_HELP object is INVALID.

Owner Object Name Object type Status
APEX_030200 WWV_FLOW_HELP PACKAGE BODY INVALID

You can also check the invalid objects using below query

SQL> select owner,object_name from dba_objects where status='INVALID';
OWNER OBJECT_NAME
------------------------------ --------------------
APEX_030200 WWV_FLOW_HELP

Run utlrp.sql to compile all invalid objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

The utlrp.sql output has below error message
FAILED CHECK FOR PACKAGE BODY WWV_FLOW_HELP

To find the errors query DBA_ERRORS table or complete the package.

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

SQL> show errors
Errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
188/20 PL/SQL: SQL Statement ignored
189/27 PL/SQL: ORA-00942: table or view does not exist
191/13 PL/SQL: Statement ignored
191/13 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
196/16 PL/SQL: SQL Statement ignored
197/23 PL/SQL: ORA-00942: table or view does not exist
199/9 PL/SQL: Statement ignored
199/9 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
261/5 PL/SQL: Statement ignored
261/5 PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be
Declared

SQL> SELECT NAME,TYPE,TEXT from dba_errors where owner='APEX_030200'

NAME TYPE
------------------------------ ------------
TEXT
--------------------------------------------------------------------------------
WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DOC.FILTER' must be declared

WWV_FLOW_HELP PACKAGE BODY
PL/SQL: Statement ignored

WWV_FLOW_HELP PACKAGE BODY
PLS-00201: identifier 'CTX_DDL.CREATE_PREFERENCE' must be declared

It’s clear that APEX_030200 user does not have privilege on CTX_DDL and CTX_DOC, need to grant the execute privileges on CTX_DDL and CTX_DOC to APEX user.

SQL> grant execute on ctx_ddl to APEX_030200;
Grant succeeded.
SQL> grant execute on ctx_doc to APEX_030200;
Grant succeeded.

Now recompile the APEX_030200.WWV_FLOW_HELP package

SQL> alter package APEX_030200.WWV_FLOW_HELP compile;
Package altered.
SQL> alter package APEX_030200.WWV_FLOW_HELP compile body;
Package body altered.

Now you should able to see that Application Express component is invalid.

SQL> select comp_name, version, status from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express VALID 3.2.1.00.10

If there are no INVALID objects and if you still see the component as invalid then run below commands as SYS to reset the DBA_REGISTRY.

SQL> exec validate_apex; (If your APEX version is 2.0 or above)
OR
SQL> exec validate_htmldb; (If your APEX version is prior to 2.0)

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com