DROP TABLE my_schema.myexternaltable;

CREATE TABLE my_schema.myexternaltable
(
  site                NUMBER(5),
  check_nbr           NUMBER(6),
  check_date          DATE,
  amount              NUMBER,
  first_name          VARCHAR2(25),
  mid_init            CHAR(1),
  last_name           VARCHAR2(35),
  address_line        VARCHAR2(40),
  address_city        VARCHAR2(30),
  address_state       CHAR(2),
  address_country     VARCHAR2(30),
  address_zip         VARCHAR2(10),
  ssn                 VARCHAR2(9),
  do_code             CHAR(2),
  acctable_user_code  CHAR(3)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY file_dir
     ACCESS PARAMETERS 
       (RECORDS DELIMITED BY NEWLINE
       BADFILE file_dir:'\load\badfile\check4cash.bad'
       DISCARDFILE file_dir:'\load\discardfile\check4cash.dsc'
       LOGFILE file_dir:'\load\logfile\check4cash.log'
       SKIP 0
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"' AND '"'
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         site CHAR(5),
         check_nbr CHAR(6),
         check_date CHAR(10) DATE_FORMAT DATE MASK "mm/dd/yyyy",
         amount CHAR,
         first_name CHAR(25),
         mid_init CHAR(1),
         last_name CHAR(35),
         address_line CHAR(40),
         address_city CHAR(30),
         address_state CHAR(2),
         address_country CHAR(30),
         address_zip CHAR(10),
         ssn CHAR(9),
         do_code CHAR(2),
         acctable_user_code CHAR(3)
       ) )
     LOCATION (dds_file_dir:'\load\CheckForCash.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
/

ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY file_dir
     ACCESS PARAMETERS 
       (RECORDS DELIMITED BY NEWLINE
       BADFILE file_dir:'check4cash.bad'
       DISCARDFILE file_dir:'check4cash.dsc'
       LOGFILE file_dir:'check4cash.log'
       SKIP 0
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"' AND '"'
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         site CHAR(5),
         check_nbr CHAR(6),
         check_date CHAR(10) DATE_FORMAT DATE MASK "mm/dd/yyyy",
         amount CHAR,
         first_name CHAR(25),
         mid_init CHAR(1),
         last_name CHAR(35),
         address_line CHAR(40),
         address_city CHAR(30),
         address_state CHAR(2),
         address_country CHAR(30),
         address_zip CHAR(10),
         ssn CHAR(9),
         do_code CHAR(2),
         acctable_user_code CHAR(3)
       ) )
     LOCATION (dds_file_dir:'\load\CheckForCash.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
/