Oracle External Table
The data stored in file in a predefined OS directory can be loaded into Oracle database using the external table feature. The DBA setup for directory and table creation script are shown below. This simplifies the loading of data ( ETL process in EDW table loading) from OS files in text, csv, format etc. into an Oracle database. The data in the file(s) should match the order of the columns in the external table.

Grants/Privilege Setup
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;

External Table Setup
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; 

Data Loading From External Table
-- 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');


Oracle registered trademark of Oracle Corporation.

Last Revised On: December 08, 2013

  1426