I just upgraded to Oracle 18c XE from 11g. I have an external table that worked fine on 11g, but I keep getting the following errors on 18c.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11604: no directory object specified for log file
The directory does exist and I have the correct grants.
SELECT *
FROM all_directories
WHERE directory_name = 'MYDIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
SYS MYDIR C:\Users\sneuf\OneDrive\PLSQL\OracleDirectory 1
SELECT *
FROM all_tab_privs
WHERE table_name = 'MYDIR'
AND grantee = 'C##_SNEUF';
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED
SYS C##_SNEUF SYS MYDIR EXECUTE YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR READ YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR WRITE YES NO NO DIRECTORY NO
I'm pretty sure I'm missing a grant somewhere, but I can't figure out what.
Here is my table:
CREATE TABLE C##_SNEUF.CHECKING_TBL_EXT2
(
DB_KEY NUMBER,
CHECK_NUM VARCHAR2(10),
TRANS_DATE TIMESTAMP (6),
DESCRIPTION VARCHAR2(100),
DEPOSIT_WITHDRAWAL VARCHAR2(1),
AMOUNT VARCHAR2(12),
MEMO VARCHAR2(200)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE MYDIR: 'checking.bad'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
LDRTRIM
MISSING FIELD VALUES ARE NULL
(
DB_key CHAR,
check_num CHAR(10),
trans_date CHAR(21) DATE_FORMAT DATE MASK 'MM/DD/YYYY HH24:MI:SS',
description CHAR(100),
deposit_withdrawal CHAR(1),
amount CHAR(12),
memo CHAR(200)
)
)
LOCATION
( MYDIR: 'checking.csv'
)
)
REJECT LIMIT UNLIMITED ;Thanks, Steve
We need to write a log file based on the load of the data. You didn't specify one.
You can do:
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS (
which means everything comes from/goes to MYDIR, or you'd need to add:
BADFILE MYDIR: 'checking.bad'
LOGFILE MYDIR: 'checking.log'