ORACLE创建外部表及数据加载

一.外部表特性

数据文件位于操作系统之外,并且具有一定的格式分割的文本文件或其他类型文件.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_20190831.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 2013


Copyright (c) 1982, 2011, 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_20130809_badfile.dat'   --存放处理失败的记录文件描述
    DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_20130809_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_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文件