关于External Table

来源：互联网发布：广发股票交易软件编辑：程序博客网时间：2024/04/28 21:08

导读：

从oracle9i开始，通过External table，可以直接以表格的形式访问外部文件，而不需要事先通过sqlldr将文件load进数据库。oracle9i的external table是通过sqlldr引擎实现的。到了oracle10g，又增加了data pump方式的external table，该方式不但可以读取外部文件，甚至可以将数据从数据库写unload到外部文件中。通过dba_external_tables/all_external_tables/user_external_tables可以查看系统中的external tables的信息。

实验环境

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

假设一文本文件d:/test.txt含有以下数据：
360,Jane,Janus,ST_CLERK,121,17-5-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-5-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-5-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-5-2001,9000,.15,80,aalda

创建Directory

SQL> create directory dir_test as 'd:/';

目录已创建。

1.创建sqlldr驱动的External Tables

默认情况下，oracle会采用sqlldr驱动的external table

SQL> CREATE TABLE test_ext
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY dir_test
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile admin_bad_dir:'test.bad'
21 logfile admin_log_dir:'test.log'
22 fields terminated by ','
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id, manager_id,
25 hire_date char date_format date mask "dd-mm-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 LOCATION ('test.txt')
30 )
31 PARALLEL
32 REJECT LIMIT UNLIMITED;