This example tries to demonstrate how CSV files can be read with external tables
First, we need a CSV-file whose data we want to import into the database. I put this file into the directory home/rene/ext (or when on Windows into C:\Users\Rene/ext/) and named it file.csv:
Create a directory object and grant read (at least) and write to the user that wants the external table:
connect / as sysdba
create directory ext_dir as '/home/rene/ext';
-- create directory ext_dir as 'C:\Users\Rene\ext';
grant read write to rene
Create the external table:
connect rene/rene
create table ext_table_csv (
i number,
n varchar2(20),
m varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('file.csv')
)
reject limit unlimited;
select * from ext_table_csv;
Cleaning up:
drop directory ext_dir;
Error messages in Windows
In Windows, I have received the following error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file file.csv in EXT_DIR not found
I fixed this by giving the Oracle Service (process) the required privileges on the file (See KUP-04040).
After fixing this error, I had a KUP-04027: file name check failed error which I was able to fix by granting the necessary privileges on $home/ext (See KUP-04027)