External Table

EXAMPLE: 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,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,jason_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

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

CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to jason
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS 'E:\external\flatfiles\data';
CREATE OR REPLACE DIRECTORY admin_log_dir
    AS 'E:\external\flatfiles\log';
CREATE OR REPLACE DIRECTORY admin_bad_dir
    AS 'E:\external\flatfiles\bad';
    /
GRANT READ ON DIRECTORY admin_dat_dir TO jason;
GRANT WRITE ON DIRECTORY admin_log_dir TO jason;
GRANT WRITE ON DIRECTORY admin_bad_dir TO jason;
-- jason connects
CONNECT jason/jason
-- create the external table
CREATE 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 jason employees table
INSERT 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;
/
ALTER TABLE admin_ext_employees
    ACCESS PARAMETERS
       (FIELDS TERMINATED BY ';');
/
ALTER TABLE admin_ext_employees
   LOCATION ('empxt3.txt',
             'empxt4.txt');
/
ALTER TABLE admin_ext_employees
    DEFAULT DIRECTORY admin_dat2_dir;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25323853/viewspace-707910/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25323853/viewspace-707910/