Friday, April 6, 2012

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

3 comments:

  1. Your article pointed me in the right direction. However, I need to install the CTXSYS schema,aka "Oracle Text" in my database as well, since the objects CTX_DDL and CTX_DOC didn't exist. To do so, follow the document "Manual Installation, Deinstallation and Verification of Oracle Text 11gR2 (Doc ID 970473.1)" on My Oracle Support.

    ReplyDelete
  2. Search 1000s of job offers and find the best suitable position. Jobs near me from direct employers no agencies or jobs direct from top recruitment agencies & recruitment consultants. Send your job application to apply for a job with one click.

    if you want more just look here "applytojobs"

    ReplyDelete