加载带有 Windows 行结尾的 CSV 文件时，Oracle Sql Loader“ORA-01722：无效数字”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22784992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Sql Loader "ORA-01722: invalid number" when loading CSV file with Windows line endings
提问by daniilyar
I am using Oracle Sql Loader Utility from Linux shell to load csv data into Oracle DB. But I have noticed that if source csv files lines endings are '\r\n' (Windows format), sqlldr fails to load data for last column.
我正在使用 Linux shell 中的 Oracle Sql Loader Utility 将 csv 数据加载到 Oracle DB。但我注意到，如果源 csv 文件行结尾是 '\r\n'（Windows 格式），sqlldr 无法加载最后一列的数据。
For example, if last column is of FLOAT type (defined in ctl file as 'FLOAT EXTERNAL'), sqlldr fails with 'ORA-01722: invalid number':
例如，如果最后一列是 FLOAT 类型（在 ctl 文件中定义为“FLOAT EXTERNAL”），sqlldr 将失败并显示“ORA-01722：无效数字”：
Sqlldr ctl file:
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 execution command:
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 error log:
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
When I replaced Windows line endings to Unix ones, all errors gone and all data loaded correctly.
当我将 Windows 行尾替换为 Unix 时，所有错误都消失了，所有数据都正确加载了。
My question is: how could I specify line terminator char in sqlldr config file but still keep the source file name in shell command?
我的问题是：如何在 sqlldr 配置文件中指定行终止符字符，但仍将源文件名保留在 shell 命令中？
I've seen some examples of how to do that with stream record format http://docs.oracle.com/cd/E11882_01/server.112/e16536/ldr_control_file.htm#SUTIL1087, but these examples are not applicable in my case as I need to keep name of data file in shell command, and not inside ctl file.
我已经看到了一些关于如何使用流记录格式http://docs.oracle.com/cd/E11882_01/server.112/e16536/ldr_control_file.htm#SUTIL1087 执行此操作的示例，但这些示例不适用于我的情况因为我需要在 shell 命令中保留数据文件的名称，而不是在 ctl 文件中。
回答by arjun gaur
I recently encountered the same issue while loading data into my table via csv file. My file looked like this :
我最近在通过 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
)
And as you mentioned , i kept getting the same error 'invalid number' Turns out this usually occurs -when your column datatype is Number but data you're getting from your csv file is in string,so oracle loader fails to perform a conversion of string to number. - when your field in csv file is terminated by some delimiters ,say space,tabs etc.
正如您所提到的，我一直收到相同的错误“无效数字”结果证明这通常会发生 - 当您的列数据类型为 Number 但您从 csv 文件中获取的数据为字符串时，因此 oracle 加载程序无法执行字符串到数字。- 当 csv 文件中的字段被某些分隔符终止时，比如空格、制表符等。
This is how i altered my ctl file :
这就是我改变我的 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
)
回答by Mark Wagoner
Try using stream record formatand specifying the terminator string. From the docs
尝试使用流记录格式并指定终止符字符串。从文档
On UNIX-based platforms, if no terminator_string is specified, SQL*Loader defaults to the line feed character, \n.
在基于 UNIX 的平台上，如果未指定 terminator_string，则 SQL*Loader 默认为换行符 \n。
The terminator string should allow you to specify a combination of characters.
终止符字符串应该允许您指定字符组合。
