Problems with external tables in loading csv file

[DanDanAUS]

Dear all

I have some problems while using external tables in oracle 9i to load a csv file. The post that explains something similar is in this thread: thread759-1423819

I will explain the scenario:

My scenario is as follows:

There is a unix box that has the Oracle database. On that box there is a folder /stagingServer/data/staging.

I create this folder to be the folder that stores the data to be imported.

CREATE OR REPLACE DIRECTORY ext AS '/stagingServer/data/staging';

GRANT READ ON DIRECTORY ext TO rss;
GRANT WRITE ON DIRECTORY ext TO rss;


Then i create the external table:

CREATE TABLE QSCAT_PARAMETERS_LOAD_EXTERNAL
(
ID VARCHAR2(50),
ORBIT VARCHAR2(50),
TIME_MIN NUMBER(18,0),
WINDS_MS NUMBER(18,2),
WIND_DIR_D NUMBER(18,2),
RAIN NUMBER(18,2),
RAD_RAIN NUMBER(18,2)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY ext
ACCESS PARAMETERS
(
records delimited by newline
fields terminated by ','
reject rows with all null fields
(
ID,
ORBIT,
TIME_MIN,
WINDS_MS,
WIND_DIR_D,
RAIN,
RAD_RAIN
)
)
LOCATION ('qscat.csv')
)
PARALLEL
REJECT LIMIT 0;

then when i try to do select * from table i get:

select * from QSCAT_PARAMETERS_LOAD_EXTERNAL
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file QSCAT_PARAMETERS_LOAD_EXTERNAL_28974.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

Any help would be much much apreciated. No idea as to why this might be happening.

Thank you for your responses up front.

 

[SantaMufasa]

DanDan,

Could you please post the results of a Unix

ls -l /stagingServer/data/staging

Your error indicates that Oracle is unable to create (as output) your log file for your external table.

As an interim method to get past the error and/or to isolate the problem, I recommend that you add the following code to your table definition:

[b]...
NOBADFILE NODISCARDFILE NOLOGFILE[/b]
...
REJECT LIMIT [b]UNLIMITED[/b]

The above code adjustment will, at least, avoid Oracle's attempt to access the "/staging..." path, for output, until you have isolated the problem.

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DanDanAUS]

Thank you for the reply mate.

That helped to solve the problem. Much apreciated.

Once more thank you.

Cheers
Dan

 

[KenCunningham]

Dan Dan, perhaps you forgot to give Dave a star for his help, cobber?

I want to be good, is that not enough?