Skip to Main Content
  • Questions
  • issue with the usage of the oracle external table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, santhosh.

Asked: January 09, 2017 - 4:41 am UTC

Last updated: January 09, 2017 - 11:26 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi tom,

I have an oracle external table in my database and i am running my database in linux operating system.I have a directory where i will be placing the (.csv) file and loading into the external table.But there is an issue with the external table
when ever i open the table i am getting error as

ORA-29913:ERROR IN EXECUTING odciexttableopen callout
ORA-29400: data catridge error
error opening file:
/lhbase/ora_ext/logging/column_framework_master_list_load.log.


Here column_framework_master_list_load.csv is my csv file which contains which things to load into my external table and column_framework_master_list_load.log is its log file.

How can i get rid of this error and get the data into the table.

Regards,
Santhosh.

and Chris said...

The oracle OS user isn't able to open the file /lhbase/ora_ext/logging/column_framework_master_list_load.log. This could be because it doesn't have write privileges on the specified directory:

bash-4.1$ pwd
/tmp
bash-4.1$ cat test.txt
test.txt

bash-4.1$ ls -l
total 24
drwxr-xr-x 2 csaxon dba  4096 Nov  9 07:30 hsperfdata_csaxon
drwxr----- 2 emcadm dba  4096 Dec 25 07:32 hsperfdata_emcadm
drwxr-xr-x 2 root   root 4096 Jan  8 21:15 hsperfdata_root
-rw------- 1 csaxon dba   443 Jan  9 03:21 krb5cc_730801
dr-xr-xr-x 2 csaxon dba  4096 Jan  9 03:16 test
-rw-r--r-- 1 csaxon dba    10 Jan  9 03:12 test.txt


So I have access to write to /tmp, but not /tmp/test. So if I set the logfile location to /tmp/test, we can't read from the external table:

SQL> create or replace directory tmp as '/tmp';

Directory created.

SQL> create or replace directory tmp_test as '/tmp/test';

Directory created.

SQL> create table t (
  x varchar2(10)
) organization external (
  default directory tmp
  access parameters (
    logfile tmp_test:'test.log'
  )
  location ('test.txt')
);  2    3    4    5    6    7    8    9

Table created.

SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04001: error opening file /tmp/test/test.log


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library