cancel
Showing results for 
Search instead for 
Did you mean: 

KUP-04001: error opening file

SOLVED
Go to solution
Edward Mugerwa
Occasional Advisor

KUP-04001: error opening file

Dear All;

Am getting the following errors
-------
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04001: error opening file /home/MSC01_8684_0509221213A_EXT.dat
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
...
KUP-04001: error opening file /home/MSC01_8684_0509221213A_EXT.dat
KUP-04017: OS message: Permission denied
---

when I use external tables. My oracle version is 9.2.0.6.0 and os is HP-UX B.11.11 .

The file and directory exists and full permisions are granted. The Oracle directory exists and full grants assigned.

What could be the reason why I can't 'select' from the external table? Your help is highly appreciated.

regards;
Edward
6 REPLIES
Hakan Aribas
Valued Contributor

Re: KUP-04001: error opening file

Yogeeraj_1
Honored Contributor

Re: KUP-04001: error opening file

hi edward,

see metalink note:170974.1 "ORA-29913: ODCIEXTTABLEFETCH Callout - When Selecting from External Table"

the main reason for this error is "Wrong data in the flat file of the External Table or invalid delimiter specification in the create table statement"

Three things that you can immediately check are:
A. Check the end of the file to be 'loaded' specified by 'LOCATION'

This file should not contain an extra 'empty line'

B. Check that the external table definition matches the datafile format

After adding a column to the external table definition, a value or at least a zone for the new field has to be added in the records of the file to be 'loaded' specified by 'LOCATION'.


C. Check the TAB delimiter or the HEX delimiter

a. The TAB delimiter '\t', used by Oracle, is represented by a HEX value '09'
b. The HEX value in the datafile should match the HEX value specified in the CREATE statement
c. The HEX specification in the CREATE statement should look like 0X''


hope this helps!
regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edward Mugerwa
Occasional Advisor

Re: KUP-04001: error opening file

Thanks Guys;

Your suggestions helped trace the problem, though where not directly related.

I have established this as probably related to the HP-UX 11 handles links, soft or hard or rather how Oracle 9.2.0.6.0 interpretes these links.

If I have a normal data file I don't get the said error, however, if the same file is accessed via any sort of link I do get the errors mentioned.

Incidently am using links in my processing!!

Any suggestions on how I can resolve this is highly appreciated.

Regards;
Edward Mugerwa
Sandman!
Honored Contributor
Solution

Re: KUP-04001: error opening file

Either touch a regular file so that Oracle doesn't have to contend with symbolic links, hard or soft, or write and exception handler routine in your script to handle such a scenario.

regards!
Sandman!
Honored Contributor

Re: KUP-04001: error opening file

Also make sure that the bad file and log file can be created by Oracle and the dir is not a network mount point.

regards!
julialloyds45
Occasional Collector

Re: KUP-04001: error opening file

If you are not able to open an external table on the Oracle then it results to error:

 

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERN_17063.log
KUP-04040: file TEST.TXT in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 19

 

It is the most common ORA-29913 error is caused when the Oracle fails to open the OS files that it needs for opening. In such a situation Oracle Repair Tool can be highly helpful to overcome such a situation. It is powerful and comprehensive data recovery software that is especially designed for recovering corrupted or damaged Oracle database file and the tables and the contents in it that are exported from the external source.