oracle 如何真正跳过一列的处理？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/979774/
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
How to really skip the processing of a column?
提问by Romain Linsolas
In order to load data (from a CSV file) into an Oracle database, I use SQL*Loader.
为了将数据（从 CSV 文件）加载到 Oracle 数据库中，我使用了 SQL*Loader。
In the table that receives these data, there is a varchar2(500)column, called COMMENTS.
For some reasons, I want to ignore this information from the CSV file.
Thus, I wrote this control file:
在接收这些数据的表中，有一varchar2(500)列名为COMMENTS。由于某些原因，我想从 CSV 文件中忽略此信息。因此，我写了这个控制文件：
Options (BindSize=10000000,Readsize=10000000,Rows=5000,Errors=100)
Load Data
Infile 'XXX.txt'
Append into table T_XXX
Fields Terminated By ';'
TRAILING NULLCOLS
(
...
COMMENTS FILLER,
...
)
This code seems to work correctly, as the COMMENTSfield in database is always set to null.
此代码似乎工作正常，因为COMMENTS数据库中的字段始终设置为null.
However, if in my CSV file I have a record where the corresponding COMMENTSfield exceeds the 500 characters limit, I get an error from SQL*Loader:
但是，如果在我的 CSV 文件中有相应COMMENTS字段超过 500 个字符限制的记录，我会从 SQL*Loader 收到错误消息：
Record 2: Rejected - Error on table T_XXX, column COMMENTS.
Field in data file exceeds maximum length
Is there a way to reallyexclude the processing of my COMMENTSfields?
有没有办法真正排除对我的COMMENTS字段的处理？
采纳答案by Vincent Malgrat
I can't reproduce your problem. I'm using Oracle 10.2.0.3.0 with SQL*Loader 10.2.0.1.
我无法重现你的问题。我将 Oracle 10.2.0.3.0 与 SQL*Loader 10.2.0.1 一起使用。
Here is my test case:
这是我的测试用例：
SQL> CREATE TABLE test_sqlldr (
2 ID NUMBER,
3 comments VARCHAR2(20),
4 id2 NUMBER
5 );
Table created
Control file:
控制文件：
LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
comments filler,
id2
)
data file:
数据文件：
1;aaa;2
3;abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz;4
5;bbb;6
I'm using the command sqlldr userid=xxx/yyy@zzz control=test.ctland I'm getting all the rows without errors:
我正在使用该命令sqlldr userid=xxx/yyy@zzz control=test.ctl并且我正在获取所有行而没有错误：
SQL> select * from test_sqlldr;
ID COMMENTS ID2
---------- -------------------- ----------
1 2
3 4
5 6
You may try another approach, I'm getting the same desired result with the following control file:
您可以尝试另一种方法，我使用以下控制文件获得了相同的预期结果：
LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
comments "substr(:comments,1,0)",
id2
)
Update following Romaintaz's comment: I looked into it again and managed to get the same error as you when the size of the column exceeded 255 characters. This is because the default datatype of SQL*Loader is char(255). If you have a column with more data you will have to specify the length. The following control file solved the problem for a column with 300 characters:
根据 Romaintaz 的评论更新：当列的大小超过 255 个字符时，我再次查看并设法得到与您相同的错误。这是因为 SQL*Loader 的默认数据类型是 char(255)。如果您有一列包含更多数据，则必须指定长度。以下控制文件解决了包含 300 个字符的列的问题：
LOAD DATA
INFILE test.data
INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( id,
comments filler char(4000),
id2
)
Hope this Helps,
希望这可以帮助，
--
Vincent
——
文森特
回答by user98960
Just to suggest a tiny improvement, you might try something like:
只是建议一个微小的改进，你可以尝试这样的事情：
LOAD DATA
IN FILE test.data INTO TABLE test_sqlldr
APPEND
FIELDS TERMINATED BY ';'TRAILING NULLCOLS
(
id,
comments char(4000) "substr(:comments, 1, 200)",
id2)
Now you'll grab the first 200 characters (or any number you specify in it's place) of all comments - unless some of your input records have values for the comments field that exceed 4000 characters, in which they'll be rejected by loader with the 'exceeds max length' error noted earlier. But assuming that's rare or not the case, all the records will load with some of the comments truncated to 200 chars.
现在，您将获取所有评论的前 200 个字符（或您在其位置指定的任何数字） - 除非您的某些输入记录的评论字段值超过 4000 个字符，否则它们将被加载程序拒绝前面提到的“超过最大长度”错误。但假设这种情况很少见，所有记录都会加载一些被截断为 200 个字符的评论。
If you go over char(4000)you'll get a SQL Loader error - there's a limit to how far you can push the beast.
如果你过去，char(4000)你会得到一个 SQL Loader 错误 - 你可以推动野兽多远是有限制的。
