加载带有 Windows 行结尾的 CSV 文件时，Oracle Sql Loader "ORA-01722: invalid number"

Question

我正在 Linux shell 中使用 Oracle Sql Loader Utility 将 csv 数据加载到 Oracle DB 中。但我注意到，如果源 csv 文件行结尾为“\r\n”(Windows 格式)，sqlldr 无法加载最后一列的数据。

例如，如果最后一列是 FLOAT 类型(在 ctl 文件中定义为“FLOAT EXTERNAL”)，则 sqlldr 会失败并显示“ORA-01722:无效数字”:

Sqlldr ctl 文件:

OPTIONS(silent=(HEADER))
load data
 replace
 into table fp_basic_bd
 fields terminated by "|" optionally enclosed by '"'
 TRAILING NULLCOLS
 (
 FS_PERM_SEC_ID CHAR(20),
 "DATE" DATE "YYYY-MM-DD", 
 ADJDATE DATE "YYYY-MM-DD", 
 CURRENCY CHAR(3),
 P_PRICE FLOAT EXTERNAL,
 P_PRICE_OPEN FLOAT EXTERNAL,
 P_PRICE_HIGH FLOAT EXTERNAL,
 P_PRICE_LOW FLOAT EXTERNAL,
 P_VOLUME FLOAT EXTERNAL
 )

sqlldr执行命令:

sqlldr -userid XXX -data ./test.data -log ./test.log -bad ./test.errors -control test.ctl -errors 3 -skip_unusable_indexes -skip_index_maintenance

sqlldr错误日志:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FS_PERM_SEC_ID                      FIRST    20   |  O(") CHARACTER            
"DATE"                               NEXT     *   |  O(") DATE YYYY-MM-DD      
ADJDATE                              NEXT     *   |  O(") DATE YYYY-MM-DD      
CURRENCY                             NEXT     3   |  O(") CHARACTER            
P_PRICE                              NEXT     *   |  O(") CHARACTER            
P_PRICE_OPEN                         NEXT     *   |  O(") CHARACTER            
P_PRICE_HIGH                         NEXT     *   |  O(") CHARACTER            
P_PRICE_LOW                          NEXT     *   |  O(") CHARACTER            
P_VOLUME                             NEXT     *   |  O(") CHARACTER            

value used for ROWS parameter changed from 300000 to 65534
Record 1: Rejected - Error on table FP_BASIC_BD, column P_VOLUME.
ORA-01722: invalid number

Record 2: Rejected - Error on table FP_BASIC_BD, column P_VOLUME.
ORA-01722: invalid number

当我将 Windows 行结尾替换为 Unix 行结尾时，所有错误都消失了并且所有数据都正确加载。

我的问题是:如何在 sqlldr 配置文件中指定行终止符字符，但仍保留 shell 命令中的源文件名？

我已经看到了一些如何使用流记录格式做到这一点的示例 http://docs.oracle.com/cd/E11882_01/server.112/e16536/ldr_control_file.htm#SUTIL1087 ,但这些示例不适用于我的情况，因为我需要在 shell 命令中保留数据文件的名称，而不是在 ctl 文件内。

Answer 1

我最近在通过 csv 文件将数据加载到表中时遇到了同样的问题。我的文件如下所示:

LOAD DATA
    infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
    REPLACE
    INTO TABLE LOAN_BALANCE_MASTER_INT
    fields terminated by ',' optionally enclosed by '"'
    (
    ACCOUNT_NO,
    CUSTOMER_NAME,
    LIMIT,
    REGION,

    TERM_AGREEMENT INTEGER EXTERNAL
    )

正如您提到的，我不断收到相同的错误“无效号码”事实证明这通常会发生-当您的列数据类型为数字但从 csv 文件获取的数据为字符串时，Oracle 加载程序无法执行字符串到数字的转换。- 当 csv 文件中的字段由某些分隔符(例如空格、制表符等)终止时。

这就是我更改 ctl 文件的方式:

 LOAD DATA
    infile '/ipoapplication/utl_file/LBR_HE_Mar16.csv'
    REPLACE
    INTO TABLE LOAN_BALANCE_MASTER_INT
    fields terminated by ',' optionally enclosed by '"'
    (
    ACCOUNT_NO,
    CUSTOMER_NAME,
    LIMIT,
    REGION,

    TERM_AGREEMENT INTEGER Terminated by Whitespace
    )