external table sample : from oracle document

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         BADFILE log_dir:'sh_sales.bad'         LOGFILE log_dir:'sh_sales.log_xt'         FIELDS TERMINATED BY "|"         (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')  );

CONNECT  /  AS SYSDBA;-- Set up directories and grant access to hr CREATE OR REPLACE DIRECTORY admin_dat_dir    AS '/flatfiles/data'; CREATE OR REPLACE DIRECTORY admin_log_dir     AS '/flatfiles/log'; CREATE OR REPLACE DIRECTORY admin_bad_dir     AS '/flatfiles/bad'; GRANT READ ON DIRECTORY admin_dat_dir TO hr; GRANT WRITE ON DIRECTORY admin_log_dir TO hr; GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;-- hr connects CONNECT hr/hr-- create the external tableCREATE TABLE admin_ext_employees                   (employee_id       NUMBER(4),                     first_name        VARCHAR2(20),                    last_name         VARCHAR2(25),                     job_id            VARCHAR2(10),                    manager_id        NUMBER(4),                    hire_date         DATE,                    salary            NUMBER(8,2),                    commission_pct    NUMBER(2,2),                    department_id     NUMBER(4),                    email             VARCHAR2(25)                    )      ORGANIZATION EXTERNAL      (        TYPE ORACLE_LOADER        DEFAULT DIRECTORY admin_dat_dir        ACCESS PARAMETERS        (          records delimited by newline          badfile admin_bad_dir:'empxt%a_%p.bad'          logfile admin_log_dir:'empxt%a_%p.log'          fields terminated by ','          missing field values are null          ( employee_id, first_name, last_name, job_id, manager_id,            hire_date char date_format date mask "dd-mon-yyyy",            salary, commission_pct, department_id, email          )        )        LOCATION ('empxt1.dat', 'empxt2.dat')      )      PARALLEL      REJECT LIMIT UNLIMITED; -- enable parallel for loading (good if lots of data to load)ALTER SESSION ENABLE PARALLEL DML;-- load the data in hr employees tableINSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,                       hire_date, salary, commission_pct, department_id, email)             SELECT * FROM admin_ext_employees;

 

 

http://www.2cto.com/database/201303/198698.html