外部表 External Table

 

 External tables access data in external sources as if it were in a table in the database.

You can connect to the database and create metadata for the external table using DDL.

The DDL for an external table consists of two parts: one part that describes the Oracle

column types, and another part (the access parameters) that describes the mapping of

the external data to the Oracle data columns.

创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

数据在数据库的外部组织，是操作系统文件。

操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

数据是只读的。（外部表相当于一个只读的虚表）

不可以在上面运行任何 DML 操作，不可以创建索引。

可以查询操作和连接。可以并行操作。

建立外部表的步骤：

1、创建以“,”分隔的文件:

在PL/SQL DEVELOP 里,

select * from emp where deptno=10;

select * from emp where deptno=20;

select * from emp where deptno=30;

用EMP表生成3个部门的数据文件,导出为CSV格式,命名为EMP10.CSV,EMP20.CSV,EMP30.CSV,去掉标题,记得哈...

2、创建一个Directory：

create directory TestTable_diras 'D:/Test' ;

目录问题导致了我折腾了半天多的时间,按照引用的这篇BLOG里的建立情况,应该是没有什么问题,可是不知道自己的操作系统的配置环境有什么问题,我的是,WIN XP ,Oracle9i Enterprise Edition Release 9.2.0.1.0 老是提示以下错误:

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错( a- z% K. Q/ h3 d A' /

ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access parameters

今天早上发现竟然是目录造成的,网上查了很久没有我的问题的解决方案,突然记得之前也出现类似目录情况,因此试了下,把目录改成create or replace directory TestTable_diras 'D:/Test/' ; 多了一"/"后就解决了,应该属于个例....郁闷了半天啊..

3、创建一个外部表：

(这个表可是用了我一天的时间,GOOGLE了N篇帖子,环境不同(数据库编码,字符集,语言)都会造成相应的问题,测试时通过的,我遇到的主要问题是日期和字符长度)

CREATE TABLE EXT_EMP (

emp_id number(4),

ename varchar2(12),

job varchar2(12) ,

mgr_id number(4) ,

hiredate date,

salary number(8),

comm number(8),

dept_id number(2)

)

ORGANIZATION EXTERNAL(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY DIR_TEST

ACCESS PARAMETERS(

RECORDS DELIMITED BY NEWLINE

badfile 'bad.txt'

FIELDS TERMINATED BY ','

optionally enclosed BY '"'

missing field VALUES are NULL

(

emp_id,

ename,

job,

mgr_id,

hiredate date "YYYY-MM-DD HH24:MI:SS",

salary,

comm ,

dept_id

)

)

LOCATION('emp10.csv','emp20.csv','emp30.csv')

)REJECT LIMIT UNLIMITED

对上面的SQL中的几个主要部分做个简单的说明：

Ø Create table：这部分代码与标准的表一样，并且在里面指定字段名等内容，没有特别的地方

Ø ORGANIZATION EXTERNAL: 这个子句就表明现在声明的是一个外部表而不是一张普通的表

Ø DEFAULT DIRECTORY temp: 这个子句指定外部表的文件在哪个目录中取得

Ø Type oracle_loader: 数据转换驱动器，oracle_loader为默认，也可以改换其他

Ø Accessparameters: 设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符

Ø RECORDS DELIMITED BY NEWLINE: 这个子句说明文本文件中的每一行就是一个记录

Ø FIELDS TERMINATED BY ',': 这个子句用于表示各个字段间用什么来分隔

Ø MISSING FIELD VALUES ARE NULL: 这个子句说明如果一个记录中某个字段的值没有，则按“空”处理

Ø (....hiredate date "YYYY-MM-DD HH24:MI:SS",.....)这个日期格式一定要完整的把表所有字段都列入其中,否则报错, 它用于指定日期型字段的格式码或许其它字段格式，这个格式码将直接与文件中的格式相对应，这样才能实现数据的正确读取和导入

Ø Optionally enclosed BY ' " ' : 数据使用"作为数据格式符号

Ø LOCATION('data.txt'): 这个子句用于指明外部文件的文件名+目录名，就能在操作系统中找到外部文件了

外部表的错误处理

1、REJECT LIMIT子句

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。

* 默认的REJECT LIMIT值为0

* REJECT LIMIT UNLIMITED则不会报错

2、BADFILE 和 NOBADFILE 子句

在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中

使用NOBADFILE子句则表示忽略转换错误的数据

● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件

● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。

3、LOGFILE 和 NOLOGFILE 子句

在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

使用NOLOGFILE子句则表示不记录错误信息到log中

● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件

例如:我修改了EMP20.CSV中的一个月份改成15月,查看相应的日志得到如下信息:

error processing column HIREDATE in row 5 for datafile d:/temp/emp20.csv

ORA-01843: not a valid month

同时查看相应的BAD.TXT里可以看到那条错误记录:

"7902","FORD ","ANALYST","7566","1981-15-03","3000.00","","20"

修改外部表语句

外部表与堆表一样可以之用ALTER TABLE命令修改表属性

* REJECT LIMIT --错误数

* DEFAULT DIRECTORY --默认目录

* ACCESS PARAMETERS --参数

* LOCATION --数据文件

* ADD COLUMN --增加列

* MODIFY COLUMN --列定义

* DROP COLUMN --删除列

* RENAME TO --外部表更名

其他约束 (外部表作为sqlldr的替换产品，在使用范围上，还是有一定的局限性质)

● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。

● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable

● 外部表不支持多用户的并发修改，也不支持LOB类型，而且访问路径中的文件必须在服务器端

查看外部表信息:

SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERS FROM　DBA_EXTERNAL_TABLES;

SELECT * FROM DBA_EXTERNAL_LOCATIONS.

最后附上自己的源代码：

test.txt 内容：

abc,abcdef,fefasdf,2011-12-21

abc,abcdef,fefasdf,2011-12-21

abc,abcdef,fefasdf,2011-12-21

abc,abcdef,fefasdf,2011-12-21

abc,abcdef,fefasdf,2011-12-21

代码：

create or replace directory dir_ext_test as 'f:/dir_ext_test/';

create table tb_ext_test

(

a varchar2(200),

b varchar2(200),

c varchar2(200),

d date

)

organization external

(

type oracle_loader

default directory dir_ext_test

access parameters

(

records delimited by newline

fields terminated by ','

MISSING FIELD VALUES ARE NULL

(a,b,c,d date "yyyy-mm-dd" )

)

location ('test.txt')

)

reject limit unlimited;

select *

from tb_ext_test;

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

转载于:http://blog.itpub.net/26622598/viewspace-715654/