1

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
Improve this question
3
  • 5
    Check the write protection tab on your floppy disk. Commented Aug 23, 2013 at 22:01
  • LOL!!! The return of the Floppy Disk! Oracle 11g reading from a floppy disk is priceless. Note to dolm77 - move the file to your hard drive before trying to import it. Commented Aug 23, 2013 at 22:47
  • The ORGANISATION code is a section of the CREATE TABLE command, not separate code. Commented Aug 24, 2013 at 9:14

3 Answers 3

Reset to default
4

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

A drive is a variable for another mapped drive. It actually is J. Sorry I should have been specific. The path is J:\proxy\proxy and on error 3 it refers to the log file that is created when I would run the SELECT statement. The true filename is "FILENAME.csv" which resides on a remote server where the database is located. So the .csv is not on my local drive it's on the default directory for external tables which is called DATA_TEMP. What are some basic questions I should be asking to determine my issue?
2

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.

Improve this answer

Comments

0

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/

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.