一.外部表特性
数据文件位于操作系统之外,并且具有一定的格式分割的文本文件或其他类型文件.Oracle的外部表通过SQL的形式访问数据文件中的数据,数据并不需要加载到数据库中且数据是可读的,所以不用dml操作,创建索引
二.创建外部表的步骤
a.创建目录对象,这一点限制数据必须的Server端
b.数据文件的准备
数据文件要求为操作系统之外且固定格式,不能有标题
c.创建外部表的字段如有特殊字段需用双引号”SYS_ID#”
d.删除外部表及目录,应先删除表后再删除目录
三.创建外部表实列
创建目录:
create or replace directory SQLDR
as 'D:\oracle\oradata\data';
创建外部表:(通过sqlldr方式的日志产生)
-- Create table
create table SYS_SQLLDR_X_EXT_MUREX_GL_TEMP
(
areano VARCHAR2(20),
currency VARCHAR2(20),
apcode VARCHAR2(20),
orgcde VARCHAR2(20),
damount VARCHAR2(200),
camount VARCHAR2(200),
remakr VARCHAR2(200)
)
organization external
(
type ORACLE_LOADER
default directory SQLDR
access parameters
(
RECORDS DELIMITED BY 0X'0A'
BADFILE 'SQLDR':'MUREX_GLRCN_INIT_badfile.dat'
DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_disfile.dat'
LOGFILE 'murex_gl_temp.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY ' ' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"AREANO" CHAR(255)
,
"CURRENCY" CHAR(255)
,
"APCODE" CHAR(255)
,
"ORGCDE" CHAR(255)
,
"DAMOUNT" CHAR(255)
,
"CAMOUNT" CHAR(255)
,
"REMAKR" CHAR(255)
)
)
location (SQLDR:'MUREX_GLRCN_INIT_0831.dat')
)
reject limit UNLIMITED;
四.sqlldr产生外部表的ddl语句
在命令运行sqlldr(控制文件事先要准备好):
a.用sqlldr生成log文件
sqlldr user_name/password@oradb control=control_file.ctl external_table=gernerate_only;
NOT_USED：默认值。
EXECUTE：这个值说明SQLLDR不会生成并执行一个SQLINSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。
GENERATE_ONLY：使SQLLDR并不具体加载任何数据，而只是会生成所执行的SQL DDL和DML语句，并放到它创建的日志文件中。
注：DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。
D:\oracle\oradata\data>sqlldr boc_rwa3/a@rwadb control=murex_gl_temp.ctl externa
l_table=generate_only
SQL*Loader: Release 11.2.0.3.0 - Production on 星期四 9月 12 22:27:03
Copyright (c) 1982, , Oracle and/or its affiliates. All rights reserved.
b.从log文件中提取ddl语句
CREATE TABLE "SYS_SQLLDR_X_EXT_MUREX_GL_TEMP"
(
"AREANO" VARCHAR2(20),
"CURRENCY" VARCHAR2(20),
"APCODE" VARCHAR2(20),
"ORGCDE" VARCHAR2(20),
"DAMOUNT" VARCHAR2(200),
"CAMOUNT" VARCHAR2(200),
"REMAKR" VARCHAR2(200)
)
ORGANIZATION external
(
TYPE oracle_loader --指定外部表的访问方式，9i不支持oracle_datapump
DEFAULT DIRECTORY SQLDR --目录
ACCESS PARAMETERS --配置外部表参数
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK --记录以换行为结束
BADFILE 'SQLDR':'MUREX_GLRCN_INIT_0809_badfile.dat' --存放处理失败的记录文件描述
DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_0809_disfile.dat'--存放处理丢弃的记录文件描述
LOGFILE 'murex_gl_temp.log_xt' --日志文件
READSIZE 1048576 --Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式则从SGA分配
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY " " LDRTRIM --"|"描述字段的终止符," "作用符
REJECT ROWS WITH ALL NULL FIELDS ----所有为空值的行被跳过并且记录到bad file
( ---下面是描述外部文件各个列的定义
"AREANO" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"CURRENCY" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"APCODE" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"ORGCDE" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"DAMOUNT" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"CAMOUNT" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"REMAKR" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " "
)
)
location
(
'MUREX_GLRCN_INIT_0809.dat' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED--描述允许的错误数，此处为无限制
;
这里特别提示一下换行符:因为在处理这个问题时,处理一天才把问题找到:
windows换行是\r\n，十六进制数值是：0D0A
LINUX换行是\n，十六进制数值是：0A
所以我们确定分隔符先用UE查看分隔符,然再改动创建外部表的语句,否则会加载不成功
a.用UE查看源文件(十六进制前)
b.用UE查看源文件(十六进制后)
由于在源文件换行符为0A,所以换行符应改为:
RECORDS DELIMITED BY 0X'0A' --0X表示十六进行,0A为linux换行符.
所以此方法适用于确定换行符.
五.外部表定义的几个重点
ANIZATIONEXTERNAL关键字，必须要有。以表明定义的表为外部表。 b.重要参数外部表的类型 ORACLE_LOADER：定义外部表的缺省方式，只能只读方式实现文本数据的装载。 ORACLE_DATAPUMP：支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也可以从内部表卸载数据作为二进制文件填充到外部表。 c.DEFAULTDIRECTORY：缺省的目录指明了外部文件所在的路径 d.LOCATION：定义了外部表的位置 f.ACCESS PARAMETERS：描述如何对外部表进行访问 RECORDS关键字后定义如何识别数据行 DELIMITEDBY'XXX'——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，如特殊符号，可以使用OX'十六位值'，例如tab(/t)的十六位是9，则DELIMITEDBY0X'09'；cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。 SKIP X ——跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。 FIELDS关键字后定义如何识别字段，常用的如下： FIELDS：TERMINATEDBY'x'——字段分割符。 ENCLOSEDBY'x'——字段引用符，包含在此符号内的数据都当成一个字段。例如一行数据格式如："abc","a""b,""c,"。使用参数TERMINATEDBY','ENCLOSEDBY'"'后，系统会读到两个字段，第一个字段的值是abc，第二个字段值是a"b,"c,。 LRTRIM ——删除首尾空白字符。 MISSING FIELDVALUESARENULL——某些字段空缺值都设为NULL。 对于字段长度和分割符不确定且准备用作外部表文件，可以使用UltraEdit、Editplus等来进行分析测试，如果文件较大，则需要考虑将文件分割成小文件并从中提取数据进行测试。外部表对错误的处理 REJECT LIMIT UNLIMITED 在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制 BADFILE和NOBADFILE子句 用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误如果未指定该参数，则系统自动在源目录下生成与外部表同名的.BAD文件BADFILE记录本次操作的结果，下次将会被覆盖 LOGFILE和NOLOGFILE子句 同样在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中而NOLOGFILE子句则表示不记录错误信息到log中，如忽略该子句，系统自动在源目录下生成与外部表同名的.LOG文件
