ORA-29913: error in executing ODCIEXTTABLEOPEN callout

While running oracle datapatch in 12c, ORA-29913 error raised due to corrupted external table. The error you might see while running the datapatch after patching.

$ ./datapatch -verbose

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 18522516
DBD::Oracle::st execute failed: ORA-04063: package body "SYS.DBMS_QOPATCH" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_QOPATCH"
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE
x XMLType;
BEGIN
x := dbms_qopatch.get_pending_activity;
? := x.getStringVal();
END;" with ParamValues: :p1=undef] at /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/sqlpatch.pm line 1293.

or

Connecting to database...OK
catcon: ALL catcon-related output will be written to /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_53150146_2017_05_09_21_37_14/sqlpatch_catcon__catcon_53150146.lst
catcon: See /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_53150146_2017_05_09_21_37_14/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_53150146_2017_05_09_21_37_14/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
DBD::Oracle::db selectrow_array failed:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-00600: internal error, arguments [kudmpgnc:badbl] [0] [] [] [] (DBD ERROR: error possibly near <*> indicator at char 7 in 'SELECT <*>dbms_sqlpatch.verify_queryable_inventory FROM dual') [for Statement "SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual"] at

Solution:

The issue is caused by corrupted external table SYS.OPATCH_XML_INV or the table does't exist in database. We tried to query the external table and we received the error ORA-29913.

SQL> select * from OPATCH_XML_INV;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "column,
disable_directory_link_check, fields, file_format, logfile, nologfile,
preprocessor, readsize, records"
KUP-01008: the bad identifier was: ┐┐┐┐┐┐┐┐┐┐
KUP-01007: at line 1 column 1

2.) Drop the table if its already exist.

SQL> drop table OPATCH_XML_INV;

3.) Re-create the external table, do not change any directory or variable.

SQL> CREATE TABLE opatch_xml_inv
(
xml_inventory CLOB
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY opatch_script_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
READSIZE 67108864
preprocessor opatch_script_dir:'qopiprep.bat'
BADFILE opatch_script_dir:'qopatch_bad.bad'
LOGFILE opatch_log_dir:'qopatch_log.log'
FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION(opatch_script_dir:'qopiprep.bat')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;

Once the above statement is successful, compile the DBMS_QOPATCH and see all the DBMS_QOPATCH objects are valid.

SQL> alter package sys.DBMS_QOPATCH compile body ;

SQL> select owner, object_name,object_type,status FROM dba_objects where object_name in ('DBMS_QOPATCH' ,'OPATCH_XML_INV');

4)Execute the datapatch -verbose again.

  $ ./datapatch -verbose

Note:- Make sure to test the solution in test environment before doing any changes in prod database and also submit the SR# with oracle support. Test!! Test!! TesT!!