Re: external table

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 12 Sep 2003 05:09:06 -0700
Message-ID: <1a75df45.0309120409.2a2a3226_at_posting.google.com>

helpora_at_yahoo.com (Erohsik) wrote

> I am using external tables to load data from a data file to an Oracle
> tables (instead of using SQLLoader).
 <snipped>
> Now my request: I need to chang the code so that the log files and the
> bad files go to a specified
> directory which I specify in the 'create directory.... (.sql) script.
>
> How to specify that ?

Something like:

CREATE OR REPLACE DIRECTORY csv AS '/csv'

CREATE OR REPLACE DIRECTORY log AS '/log'

CREATE OR REPLACE DIRECTORY dump AS '/dump'

CREATE TABLE foo_external (

   place_name   varchar2(100),
   postcode     varchar2(4),
   streetcode   varchar2(4),
   town_name    varchar2(100)

)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY CSV
 ACCESS PARAMETERS
 (
 RECORDS DELIMITED BY newline
 BADFILE DUMP:'postcodes.bad'
 DISCARDFILE DUMP:'postcodes.dis'
 LOGFILE LOG:'postcodes.log'
 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'  (

   place_name   char(100),
   postcode     char(4),
   streetcode   char(4),
   town_name    char(100)

  )
 )
 LOCATION ('postcodes.csv')
)
REJECT LIMIT UNLIMITED;

--
Billy

Received on Fri Sep 12 2003 - 14:09:06 CEST