CSV files as Oracle External Tables

God do I love this feature of oracle!  You can use SQL loader to parse a CSV file and import it into a table but what you can do is define an external table which links to a CSV file, so when you do select * from external_table; you get the csv file returned as a table.  This is incredibly useful for interface tables for importing data into your apps.

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.