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