create an external table

XAMPLE: Creating an External Table and Loading Data

The file empxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

The following hypothetical SQL statements create an external table in the hr schema named admin_ext_employees and load its data into the hr.employees table.

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;