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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,015 views