创建外部表例子
创建外部表例子
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 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;
查询结果:
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 jrichard 6 rows selected. SQL>