CODEWITHSUNDEEP
oracle-databaseoracle11g
dolm77
dolm77

Reputation: 111

Oracle 11G, external tables and .csv file

Having an issue with external tables in Oracle 11g. I'm currently using DB Artisan to run my query. The CREATE and ORGANIZATION code executes successfully, but when I run the select statement is where I am getting the error. My .csv file is set up the same way as the layout for the EXTERNAL_TABLE with the header as such.

CREATE TABLE EXTERNAL_TABLE
(
    COL1 NUMBER(14),
     COL2 VARCHAR2(10),
     COL3 VARCHAR2(3),
     COL4 VARCHAR2(3),
     COL4 VARCHAR2(4),
     COL6 NUMBER(4,0),
     COL7 VARCHAR2(20),
     COL8 VARCHAR2(20),
     COL9 NUMBER(3)    
)
ORGANIZATION EXTERNAL 
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY FOLDER1
    ACCESS PARAMETERS
    (   
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (COL1 CHAR(14),
    COL2 CHAR(10),
    COL3 CHAR(3),
    COL4 CHAR(3),
    COL4 CHAR(4),
    COL6 CHAR(4),
    COL7 CHAR(20),
    COL8 CHAR(20),
    COL9 CHAR(3)                      
                )
    )
    LOCATION ('FILENAME.csv') --Name of flat file.
)
REJECT LIMIT 0;

Running:

SELECT * FROM EXTERNAL_TABLE

gives this error:

ORA-29913:error in executing ODCIEXTTABLEOPEN callout
ORA-29400:data cartridge error
KUP-04027:file name check failed: A:\job\job\FILENAME_1234_5566.log

Upvotes: 1

Views: 6575

Answers (3)

Brian McGinity
Brian McGinity

Reputation: 5935

If you're in RAC and the file in inside of an ACFS then use:

DISABLE_DIRECTORY_LINK_CHECK

Fixes it!

see https://www.realdbamagic.com/using-external-table-windows-rac-acfs/

Upvotes: 0

Joost Evertse
Joost Evertse

Reputation: 1075

Ok, this question was asked a long time ago, but I have encountered this issue myself recently.

Problem is dat Oracle External tables does not completely support hard links that refer to remote network locations.
What I did to make it work was to create a directory object that specifically uses UNC paths (like '\remoteserver\proxy' to point to the remote network location. That magically worked for me.

Upvotes: 2

APC
APC

Reputation: 146239

KUP-04027:file name check failed means the file location is not valid.

So, given this file name ...

A:\job\job\FILENAME_1234_5566.log

... several thoughts occur:

  1. As @jonearles points out, the A: was traditionally reserved for the floppy disk drive. Does your database server (i.e. the machine which hosts your database) have an A: drive? If not you should correct that.
  2. Does that drive really really have a root directory called job with a sub-directory called job? Or is that a typo in the path of your DIRECTORY object folder1?
  3. Also, your sample code gives the file name as 'FILENAME.csv'. Obviously that doesn't match the file name in the error message. What do you actually specify for the file name in your external table definition?
  4. The exact causes of KUP-04027 are specific to the operating system, because file naming conventions are peculiar to each OS. My guess is that your database really resides on a remote Unix server but you're trying to read a file from your local Windows PC. If that is your scenario it won't work: you'll need to upload the file to the database server.

Upvotes: 4

Related Questions