How to define the external table
First you need to create directory object in database for where your csv file is going to be:
create or replace directory importDir as '/var/importData';
(you might need to grant read/write privileges to the dir object)
grant read, write on directory importDir to username;
Then create the external table as follows.
create table external_csv_file (
action varchar2(32),
event varchar2(64),
action_date date,
forename varchar2(50),
surname varchar2(50)
address varchar2(100),
street varchar2(100),
town varchar2(50),
postcode varchar2(10),
county varchar2(50),
country varchar2(50),
)
organization external
(
default directory importDir
access parameters (
records delimited by newline
badfile 'ext_table.bad'
discardfile 'ext_table.dis'
logfile 'ext_table.log'
fields terminated by ',' optionally enclosed by '"' lrtrim
missing field values are null
(
action, event,
action_date date 'dd/mm/yyyy',
forename, surname, address, street, town
postcode, county, country
)
)
location ('file_to_be_external_table.csv')
);Most of organization external directives are the same as sqlldr.
Note the fields are individually defined within the left & of the "fields" directive. If all your fields were characters then you could omit this but if the csv file has dates in say UK 'dd/mm/yyyy' the we must specify the date format here.
Also note the LTRIM on fields terminated by wich trims leading & trailing whitespace in the external table.
See http://download.oracle.com/docs/cd/B12037_01/server.101/b10825/et_concepts.htm for more info.
