Oracle sqlldr使用的几个注意事项

 Oracle sqlldr是将大量数据批量导入Oracle数据表的工具，直接可以在命令符下运行。

    最近同事在使用sqlldr的时候，碰到一些问题同时也做了些研究，现借题整理如下：

 

    1. “SQL*Loader-566”错误 

SQL*Loader: Release 10.2.0.1.0 - Production on星期四 5月 5 21:53:27 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

达到提交点 - 逻辑记录计数 1
SQL*Loader-501: 无法读取文件 (data_cn_01.dat)
SQL*Loader-566: 在数据文件的结尾处找到部分记录
SQL*Loader-2026: 加载因 SQL加载程序无法继续而被终止。
数据处理完成, 按任意键结束

    

    经过跟踪测试，发现是数据文件的问题。问题出在最后一行数据分隔符号后面没有回车，特别注意下面数据文件####后面还有一行空行。  

CN200780027488.5||||CN101490018||||本发明涉及新的游离碱形式或酸加成盐形式的式(I)的大环化合物
####
 

   

    2. “数据文件的字段超出最大长度”错误

    这个问题网上很容易找到答案，sqlldr对于字符类型默认长度为255，如果超过255需要指定长度，见红色下面ctl文件中的红色字体：

 

LOAD DATA 
  INFILE 'data_cn_01.dat' "STR X'0D0A232323230D0A'"
INTO TABLE tpis_pat_cn
APPEND
FIELDS TERMINATED BY '||||'
TRAILING NULLCOLS
(
  an,
  pn,
  ab CHAR(4000),
  source CONSTANT 'SIPO',
  patent_id "seq_tpis_pat_base.NEXTVAL"
) 

    

    3. “ORA-12899: 列的值太大”错误 

记录 1: 被拒绝 -表 TPIS_PAT_CN的列 AB出现错误。
ORA-12899: 列 "PIS"."TPIS_PAT_CN"."AB"的值太大 (实际值: 2800,最大值: 2000)

    

    这个错误很明显，和INSERT等DML语句提示错误一致。其错误原因在于从文本中读取的字段值超过了数据库表字段的长度，需要用Oracle函数解决： 

LOAD DATA  
  ...

TRAILING NULLCOLS
(
  ...

  ab CHAR(4000) "SUBSTRB(:ab,1,2000)",
  ...
) 

     

    4. “数据文件的字段超出最大长度”错误 

情况一：

记录 1: 被拒绝 -表 TPIS_PAT_CN的列 AB 出现错误。
数据文件的字段超出最大长度

情况二：

记录 1: 被拒绝 -表 TPIS_PAT_CN出现错误。
ORA-01461: 仅能绑定要插入 LONG列的 LONG值

    

    以上第一种错误情况是由于数据文件中的字段值真实长度超过指定的4000长度，所以提示“数据文件的字段超出最大长度”错误。可能有人准备将“CHAR(4000)”改为“CHAR(8000)”，这时就会出现第二种错误。其原因在于：字符类型在PL-SQL中做为变量存大，最大可支持32767个字节，但在SQL中通常只能够支持到4000字节(NCHAR为2000)，因此如果声明的变量长度超出了SQL中类型长度，并且变量实际值也超出类型可接受最大值时，就会触发ORA-01461错误。

    所以当数据文件中的字段值真实长度超过4000长度时，只有一个方法：将数据表字段类型改为CLOB类型或者新增加一个临时CLOB字段，sqlldr中的“CHAR(32767)”对于CLOB字段有效。导入后再通过SQL语句更新到真实字段中。

LOAD DATA  
  ...

TRAILING NULLCOLS
(
  ...

  ab_bk CHAR(32767),
  ...
) 

 

-- 将ab_bk更新到ab中的SQL语句

UPDATE TPIS_PAT_CN SET ab=SUBSTR(ab_bk,1,1000) WHERE ab_bk IS NOT NULL AND patent_id>=337462

  

    很遗憾，查阅了大量国外资料，sqlldr没有更好的方法处理值超过4000长度的非CLOB字段导入工作。所以只能有以下两种选择：

    方案一：当然在导入之前通过程序进行预处理，但这也不是件简单的事。

    方案二：忽略此字段的内容。通过在控制文件中指定“ac FILLER CHAR(32767)”即可实现忽略此字段的内容。

 

 参考资料：

     1. ORA-01461的解决过程

        http://space.itpub.net/7607759/viewspace-521189

     2. SQL Loader- Field in data file exceeds maximum length

        http://cn.forums.oracle.com/forums/thread.jspa?threadID=1105161