Example of creating an external table

Create an external table example The following SQL statements create an external table named admin_ext_employees in the hr schema and load data from the external table 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. Provide the user password (hr) when prompted.CONNECT 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;

 

Query results:

SQL>    SELECT * FROM admin_ext_employees;                                                                                                                                                                           Database Freespace Summary        361 Mark                 Jasper                    SA_REP            145 17-MAY-01       8000             .1            80 mjasper        362 Brenda               Starr                     AD_ASST           200 17-MAY-01       5500              0            10 bstarr        363 Alex                 Alda                      AC_MGR            145 17-MAY-01       9000            .15            80 aalda        402 Abby                 Applegate                 IT_PROG           103 17-MAY-01       9000             .2            60 aapplega        403 Carol                Cousins                   AD_VP             100 17-MAY-01      27000             .3            90 ccousins        404 John                 Richardson                AC_ACCOUNT        205 17-MAY-01       5000              0           110 jrichard6 rows selected.SQL>