Rem   *****
PROMPT   CREATE EXTERNAL TABLE
Rem   note that we're using only one external table definition for BOTH
Rem   loading processes - we just have to switch file names or links on OS
Rem   level to leverage the same ET framework for different operations with
Rem   different targets
Rem   this is an added value of ET, that you don't have the tight coupling
Rem   between flat file(s) and tables
Rem   *****
Rem   ***10g***
Rem   Accomodate channel_id=NUMBER - will do this as part of the
Rem   transformation
Rem   CREATE NEW FLAT FILES
Rem
REM 10gR1

set echo on

Rem *****
Rem CREATE EXTERNAL TABLE
Rem *****

DROP TABLE sales_delta_XT;


CREATE TABLE sales_delta_XT
(
  PROD_ID NUMBER,
  CUST_ID NUMBER,
  TIME_ID DATE,
  CHANNEL_ID CHAR(2),
  PROMO_ID NUMBER,
  QUANTITY_SOLD NUMBER(3),
  AMOUNT_SOLD NUMBER(10,2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE log_dir:'sh_sales.bad'
    LOGFILE log_dir:'sh_sales.log_xt'
    FIELDS TERMINATED BY "|" LDRTRIM
    (prod_id, cust_id,
     time_id CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
     channel_id, promo_id, quantity_sold, amount_sold
    )
  )
  location
  (
    'salesDec01.dat'
  )
)REJECT LIMIT UNLIMITED NOPARALLEL;
