ORACLE创建外部表及数据加载(二)
"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_20130809.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换行符.
所以此方法适用于确定换行符.
五.外部表定义的几个重点
a.ORGANIZATIONEXTERNAL关键字，必须要有。以表明定义的表为外部表。
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文件