EXAMPLE: Creating an External Table and Loading Data
The file empxt1.dat contains the following sample 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
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
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;
-- 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/