René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Loading dates with external tables in Oracle

The following file contains some holidays for the year 2007:
c:\temp\holidays.txt
New Year's Day,Jan 1 2007
Inauguration Day,
"Martin L. King, Jr. Day",Jan 15 2007
President's Day,Feb 19 2007
Memorial Day,May 28 2007
Independance Day,Jul 4 2007
Labor Day,Sep 3 2007
Columbus Day,Oct 8 2007
Veteran's Day,Nov 11 2007
Thankgsiving Day,Nov 22 2007
Christmas Day,Dec 25 2007
In order to access it from Oracle, a directory is created: 
create or replace directory c_temp as 'c:\temp';
Now, the external table can be created. The records are seperated by commas (fields terminated by ','). However, some holiday names (Martin L. King, Jr. Day) contain a comma, so they are enclosed with " in the file. Accordingly, the access parameters have a optionally enclosed by '"'.
Also, the date format (or date mask) is specified: Mon dd yyyy which specifies that a date consists of the first three letters of a month's name followed by a space followed by a two digit number specifying the day again followed by a space followed by a four digit number specifying the year. 
drop table holidays;
create table holidays (
  name varchar2(30),
  day  date
)
organization external (
  type              oracle_loader
  default directory c_temp
  access parameters (
    records delimited  by 0x'0a'
    fields  terminated by ',' optionally enclosed by '"'
    missing field values are null
    (
      "NAME",
      "DAY"  DATE "Mon dd yyyy"
    )
  )
  location ('holidays.txt')
)
reject limit unlimited;
Now, the table can be selected from: 
select * from holidays;

NAME                                               DAY
-------------------------------------------------- -------------------
New Year's Day                                     01.01.2007 00:00:00
Inauguration Day
Martin L. King, Jr. Day                            15.01.2007 00:00:00
President's Day                                    19.02.2007 00:00:00
Memorial Day                                       28.05.2007 00:00:00
Independance Day                                   04.07.2007 00:00:00
Labor Day                                          03.09.2007 00:00:00
Columbus Day                                       08.10.2007 00:00:00
Veteran's Day                                      11.11.2007 00:00:00
Thankgsiving Day                                   22.11.2007 00:00:00
Christmas Day                                      25.12.2007 00:00:00

Links

See also this link.