CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to dwloader

CREATE OR REPLACE DIRECTORY edw_etlxt_data
    AS '/edw/load/data'; 
CREATE OR REPLACE DIRECTORY edw_etlxt_log
    AS '/edw/load/log'; 
CREATE OR REPLACE DIRECTORY edw_etlxt_bad 
    AS '/edw/load/bad'; 

GRANT READ  ON DIRECTORY edw_etlxt_data TO dwloader;
GRANT WRITE ON DIRECTORY edw_etlxt_log  TO dwloader;
GRANT WRITE ON DIRECTORY edw_etlxt_bad  TO dwloader;

CREATE TABLE lookup_division_ext
(division_cd       VARCHAR2(20),
 division_name     VARCHAR2(50),
 emp_id            VARCHAR2(20),
 mgr_id            VARCHAR2(20),
 emp_fname         VARCHAR2(30),
 emp_lname         VARCHAR2(30),
 emp_mname         VARCHAR2(30),
 emp_phone         VARCHAR2(20),
 emp_email         VARCHAR2(40),
 effective_date    DATE
)
ORGANIZATION EXTERNAL 
   (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY edw_etlxt_data
    ACCESS PARAMETERS 
     ( 
       records delimited by newline 
       badfile edw_etlxt_bad:'division_xt%a_%p.bad' 
       logfile edw_etlxt_log:'division_xt%a_%p.log' 
       fields terminated by ',' 
       missing field values are null 
        (division_cd, division_name, emp_id, mgr_id,
         emp_fname, emp_lname, emp_mname, emp_phone, emp_email
         effective_date char date_format date mask "yyyymmdd" 
        ) 
     ) 
    LOCATION ('division_xt1.dat', 'division_xt2.dat') 
   ) 
   PARALLEL 
REJECT LIMIT UNLIMITED; 

-- Required if the data volume is very large

ALTER SESSION ENABLE PARALLEL DML;

-- Load data to staging table (can be a procedure)

INSERT INTO lookup_division_stg
(division_cd, division_name, emp_id, mgr_id, emp_fname,
 emp_lname, emp_mname, emp_phone, emp_email, effective_date)
SELECT
 division_cd, division_name, emp_id, mgr_id, emp_fname,
 emp_lname, emp_mname, emp_phone, emp_email, effective_date
FROM lookup_division_ext;

-- After successful data load, using java code the files
-- can be renamed using OS command (mv in UNIX/linux)
-- to load data from new files with Same Names
-- or command below to load file with New Names by altering the location
-- ALTER TABLE lookup_division_ext
--  LOCATION ('division_xt3.dat', 'division_xt4.dat');