Oracle Sql Loader 控制文件的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122226/
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
Problem with Oracle Sql Loader control file
提问by Marc
I'm trying to load some data using sql loader. Here is the top of my control/data file:
我正在尝试使用 sql loader 加载一些数据。这是我的控制/数据文件的顶部：
LOAD DATA
INFILE *
APPEND INTO TABLE economic_indicators
FIELDS TERMINATED BY ','
(ASOF_DATE DATE 'DD-MON-YY',
VALUE FLOAT EXTERNAL,
STATE,
SERIES_ID INTEGER EXTERNAL,
CREATE_DATE DATE 'DD-MON-YYYY')
BEGINDATA
01-Jan-79,AL,67.39940538,1,23-Jun-2009
... lots of other data lines.
...许多其他数据线。
The problem is that sql loader won't recognize the data types I'm specifying. This is the log file:
问题是 sql 加载器无法识别我指定的数据类型。这是日志文件：
Table ECONOMIC_INDICATORS, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ASOF_DATE FIRST * , DATE DD-MON-YY
VALUE NEXT * , CHARACTER
STATE NEXT * , CHARACTER
SERIES_ID NEXT * , CHARACTER
CREATE_DATE NEXT * , DATE DD-MON-YYYY
value used for ROWS parameter changed from 10000 to 198
Record 1: Rejected - Error on table ECONOMIC_INDICATORS, column VALUE.
ORA-01722: invalid number
... lots of similiar errors, expected if trying to insert char data into a numeric column.
...许多类似的错误，如果尝试将 char 数据插入数字列，则会出现预期的错误。
I've tried no datatype spec, all other numeric specs, and always the same issue. Any ideas?
我没有尝试过任何数据类型规范，所有其他数字规范，并且总是同样的问题。有任何想法吗？
Also, any ideas on why it's changing the Rows parameter?
另外，关于为什么要更改 Rows 参数的任何想法？
回答by Juergen Hartelt
From your example, SQL*Loader will try to evaluate the string "AL" to a number value, which will result in the error message you gave. The sample data has something looking like it could be a decimal number at third position, not second as specified int he column list.
在您的示例中，SQL*Loader 将尝试将字符串“AL”评估为一个数值，这将导致您给出错误消息。示例数据看起来像是在第三个位置可能是十进制数，而不是列列表中指定的第二个。
