Tuesday 28 February 2017

Oracle External Table Error ORA-12801 ORA-29913

Error:

SQL> select * from abc.EXT_TAB_BILLRUN;
select * from abc.EXT_TAB_BILLRUN
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout

Cause:

External Table loading rejecting the Bad records & were unable to append errors in bad log file due to permission issue

select dbms_metadata.get_ddl('TABLE','EXT_TAB_BILLRUN','abc') from dual;

.
.
   DEFAULT DIRECTORY "EXT_TABLES_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
   BADFILE 'bad_ext_tab_billrun'
   LOGFILE 'log_ext_tab_billrun'
   FIELDS TERMINATED BY '^'

[oracle9i@:/home/oracle9i>]tail -f log_ext_tab_billrun.log
      Trim whitespace same as SQL Loader
    BILLING_METHOD                  CHAR (255)
      Terminated by "^"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field SERVICE_ADDRESS
KUP-04036: second enclosing delimiter not found
KUP-04101: record 3 rejected in file /index01/collab/EXT_TAB_BILLRUN_DIR/RevenueRegister.csv
KUP-04001: error opening file /index01/collab/EXT_TAB_BILLRUN_DIR/bad_ext_tab_billrun.bad
KUP-04017: OS message: Permission denied

-rwxr-xr-x    1 collab   oinstall        627 Sep 20 00:33 bad_ext_tab_billrun.bad
[oracle9i@:/home/oracle9i>]pwd
/index01/collab/EXT_TAB_BILLRUN_DIR


Solution:
Collab: oinstall changed to oracle9i:oinstall

oracle9i@#sudo chown -R oracle9i:oinstall bad_ext_tab_billrun.bad
oracle9i@#ls -lrt bad*
-rwxr-xr-x    1 collab   oinstall        120 Aug 30 11:12 bad_tariff_input.bad
-rwxr-xr-x    1 oracle9i oinstall        627 Sep 20 00:33 bad_ext_tab_billrun.bad

  1*  select * from abc.EXT_TAB_BILLRUN
SQL> /

SERVICE
--------------------------------------------------------------------------------
CUSTOMER_REF_NUM
-------------------------------