external table

[naushi]

I created an external table and gave appropriate permissions to the owner of table like read and write permission to the directory. When I do a select * from the table I get the following error. I have not mentioned any log file in my syntax.

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EMP_EXT_13184.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I have also copied the create table syntax below.

create table research.emp_ext
(emp_id number(4), ename varchar2(12),
job varchar2(12) , mgr_id number(4) ,
hiredate date, salary number(8), comm number(8),dept_id number(2))
organization external
(type oracle_loader
default directory emp_dir
access parameters (records delimited by newline
fields terminated by ',')
location ('emp.dat'));


any help will be appreciated Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[Abhivyakti]

hi,

I also got the same error regarding hte external tables. The thing is, when i say desc the external table schema, its showing me the details of columns for the external table. But when i actually give the command select * from ext_tab then it gives me the same error.

My logic is it is not able to find the patywya to the table. There is something wrong in the driver or SQL Loader. May be the SQL Loader is not working fine. That is what pppl told me whne i told them aboutthis problem. May be meet your dba...or if you are the DBA urself , then chekc the SQL Loader....Is it allowing hte other imports and export of files. If yes then it means that the SQL Loader is working fine. But at our place there was a problem in Loader itself.

I will also wait for posts on this matter...

Abhivyakti

 

[naushi]

Abhivyakti,

Could you tell me what was the problem with sqlldr and how did you fix it. Isee that the last two errors 6512 pertain to sql ldr. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[naushi]

If any one is trying to answer this _ ihave resolved the issue.

when you do a select on the external table, it generates a log file in the directory where the file is. The owner of that OS directory needs to give read write permission to others so the log file could be written. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[Sukriti]

Try creating the external table with NOLOGFILE option as in

ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOLOGFILE
FIELDS TERMINATED BY ',' optionally enclosed by '"')

This may work