XDB.DBMS_XDBT Package is INVALID – Could mean install of Oracle Text

While Oracle XDB and Oracle Text are two independent products, package XDB.DBMS_XDBT is used to setup XDB context index. The package XDB.DBMS_XDBT requires that Oracle Text is installed.

SQL> alter package XDB.DBMS_XDBT compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY XDB.DBMS_XDBT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
34/7 PL/SQL: Statement ignored
34/7 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared
37/5 PL/SQL: Statement ignored
37/5 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared
38/5 PL/SQL: Statement ignored
38/33 PLS-00201: identifier 'CTXSYS.CTX_OUTPUT' must be declared
71/5 PL/SQL: Statement ignored
71/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
75/5 PL/SQL: Statement ignored
75/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
79/5 PL/SQL: Statement ignored
79/5 PLS-00201: identifier 'CTX_DDL.DROP_SECTION_GROUP' must be declared
83/5 PL/SQL: Statement ignored
83/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
86/5 PL/SQL: Statement ignored
86/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
90/5 PL/SQL: Statement ignored
90/5 PLS-00201: identifier 'CTX_DDL.DROP_PREFERENCE' must be declared
94/5 PL/SQL: Statement ignored
94/5 PLS-00201: identifier 'CTX_DDL.DROP_STOPLIST' must be declared

There are 2 options. Either you can choose to install oracle text or this object can be ignored or can be dropped if you don’t plan on using Oracle Text in the future.

Installing Oracle Text 11.1.0.7.0

Full guide can be found in note ‘Manual installation, deinstallation and verification of Oracle Text 11gR1 [ID 579601.1]’

sqlplus "/ as sysdba"
spool text_install.txt
@?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

Where:
change_on_install – is the ctxsys user password
SYSAUX – is the default tablespace for ctxsys
TEMP – is the temporary tablespace for ctxsys
LOCK|NOLOCK – ctxsys user account will be locked or not

The next step is to install appropriate language-specific default preferences – all the available default preferences are in the $ORACLE_HOME/ctx/admin/defaults directory:

connect "CTXSYS"/"change_on_install"
@?/ctx/admin/defaults/drdefuk.sql;

To then fix the invalid package:

grant execute on ctxsys.ctx_ddl to XDB; 
grant execute on ctxsys.ctx_output to XDB; 
grant ctxapp to XDB; 

alter user ctxsys account lock password expire;

@?/rdbms/admin/dbmsxdbt.sql

## Recompile package

alter package XDB.DBMS_XDBT compile; 
alter package XDB.DBMS_XDBT compile body; 
show errors package body XDB.DBMS_XDBT 

spool off

Validate the Install of Oracle Text for 11.1.0.7

-- List version
SELECT comp_name, status, substr(version,1,10) AS version
FROM dba_registry WHERE comp_id = 'CONTEXT';

SELECT * FROM ctxsys.ctx_version;

SELECT substr(ctxsys.dri_version,1,10) VER_CODE FROM dual;

-- Should be 372 objects
SELECT count(*) FROM dba_objects 
WHERE owner='CTXSYS';

-- invalid objects
SELECT object_name, object_type, status 
FROM dba_objects 
WHERE owner='CTXSYS' and status != 'VALID' 
ORDER BY object_name;