external table sample : from oracle document

最新推荐文章于 2024-12-29 21:32:31 发布

转载最新推荐文章于 2024-12-29 21:32:31 发布 · 529 阅读

本文详细介绍了如何使用Oracle LOADER加载外部数据到Oracle表格中，并通过HR连接进行数据加载，包括设置目录、访问参数及语法应用。

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 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 hr 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;

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