Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

File not found when querying external table.

Epic FailMar 1 2018 — edited Mar 1 2018

I'm running Oracle 12cR2 stand alone ASM on Oracle Linux.

I can read the file in question using utl_file but when I try to use it as an external table I get KUP-04040.

Steps Taken:

Create OS directory and place file :

[oracle]# mkdir -p /home/oracle/load/data_files

[oracle]# scp <source> /home/oracle/load/data_files/table.csv

[oracle]# cd /home/oracle

[oracle]# chown -R oracle:oinstall ./load

create directory object:

(as sys)

sql> create or replace directory load_dir as '/home/oracle/load/data_files';

sql> grant read, write, execute on directory load_dir to user1;

(as user1)

sql> CREATE TABLE "USER1"."TEST"

   ( "COL_A" char(30), "COL_B" char(30), "COL_C" char(30), "COL_D" char(30), "COL_E" char(20) )

   ORGANIZATION EXTERNAL

    ( DEFAULT DIRECTORY load_dir LOCATION ('table.csv') );

sql> select * from test;

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

KUP-04040: file table.csv in LOAD_DIR not found

29913. 00000 -  "error in executing %s callout"

*Cause:    The execution of the specified callout caused an error.

*Action:   Examine the error messages take appropriate action.

sql>

DECLARE

  l_exists     boolean;

  l_size       integer;

  l_block_size integer;

BEGIN

  utl_file.fgetattr( 'LOAD_DIR',

                     'table.csv',

                     l_exists,

                     l_size,

                     l_block_size );

     dbms_output.put_line( 'The file exists and has a size of ' || l_size );

END;

PL/SQL procedure successfully completed.

The file exists and has a size of 812

table.csv file contents are:

"11/13/2017 19:15",0,185,9.97,12.02

"11/13/2017 19:30",1,185,10.2,11.69

"11/13/2017 19:45",2,185,10.42,11.34

"11/13/2017 20:00",3,185,10.63,10.99

"11/13/2017 20:15",4,185,10.83,10.81

"11/13/2017 20:30",5,185,11.03,10.53

"11/13/2017 20:45",6,185,11.22,10.27

"11/13/2017 21:00",7,185,11.41,9.95

"11/13/2017 21:15",8,185,11.61,9.72

"11/13/2017 21:30",9,185,11.75,9.01

"11/13/2017 21:45",10,185,11.99,8.99

"11/13/2017 22:00",11,185,12.16,8.7

"11/13/2017 22:15",12,185,12.3,8.04

"11/13/2017 22:30",13,185,12.46,7.61

"11/13/2017 22:45",14,185,12.66,7.426

"11/13/2017 23:00",15,185,12.86,7.398

"11/13/2017 23:15",16,185,13.06,7.344

"11/13/2017 23:30",17,185,13.28,7.203

"11/13/2017 23:45",18,185,13.45,6.848

"11/14/2017 0:00",19,185,13.68,6.928

"11/14/2017 0:15",20,185,13.98,7.445

"11/14/2017 0:30",21,185,14.12,6.74

Message was edited by: Epic Fail

This post has been answered by Sven W. on Mar 1 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 29 2018
Added on Mar 1 2018
28 comments
3,211 views