oracle sqlldr 返回代码 - ex_warn
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7176815/
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
sqlldr return codes - ex_warn
提问by Florin Ghita
I want to know if my file is loaded complete in the database.
我想知道我的文件是否在数据库中加载完成。
if you check the return codes hereyou can see that 1 and 3 is a fail.
EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 3
EX_WARN(return code 2) includes this cases:
EX_WARN(return code 2) 包括以下情况：
All or some rows rejected EX_WARN
All or some rows discarded EX_WARN
Discontinued load EX_WARN
Now, the first and second is manageable.
现在，第一个和第二个是可以管理的。
For the third I had to search in the docs. If you read thisyou can see that "discontinued loads" include "fatal errors", "CTRL-C", and "space errors". In this cases I would probably get no records or some records rejected, EX_WARN return code, and the file incomplete loaded in database.
对于第三个，我不得不在文档中搜索。如果您阅读本文，您会看到“停止加载”包括“致命错误”、“CTRL-C”和“空间错误”。在这种情况下，我可能不会得到任何记录或某些记录被拒绝、EX_WARN 返回代码以及数据库中加载的文件不完整。
If there are no records rejected is simple: it was a discontinued load. I must exit with error. But when I have some record rejected I'm not sure that my file is completely loaded in database. (Some rows rejected is acceptable to me.) Am I right?
如果没有记录被拒绝很简单：这是一个中断的负载。我必须退出错误。但是，当我拒绝某些记录时，我不确定我的文件是否已完全加载到数据库中。（我可以接受一些被拒绝的行。）我说得对吗？
If yes, what the solution? How do I know if the entire table was loaded into DB?
如果是，有什么解决办法？我如何知道整个表是否已加载到数据库中？
回答by Gary Myers
You can have the situation where SQL Loader inserted (and committed) some rows out of a data file but failed to reach the end of that file (ie there could have been more records after the failure point which would have otherwise succeeded).
您可能会遇到 SQL Loader 从数据文件中插入（并提交）一些行但未能到达该文件末尾的情况（即，在失败点之后可能会有更多记录，否则会成功）。
I'd opt for an external table over SQL Loader, using an INSERT INTO dest_table ... SELECT * FROM external_table. That would be an atomic operation and there's a (generally small) chance that it will fail if you have insufficient undo for rollback (since you are not using intermediate commits).
我会选择 SQL Loader 上的外部表，使用INSERT INTO dest_table ... SELECT * FROM external_table. 这将是一个原子操作，并且如果您没有足够的撤消回滚（因为您没有使用中间提交），它会失败（通常很小）。
I'd also minimise the possibilities of rejections in the external table / SQL Loader layer by treating everything as generic text until it is loaded into the database. Then I'd apply the structure and use DML error logging to handle anything irregular. That way you have clear access to the rejected data and the reason for the rejection in the database.
我还通过将所有内容都视为通用文本，直到将其加载到数据库中，从而最大限度地减少外部表/SQL 加载程序层中被拒绝的可能性。然后我会应用该结构并使用 DML 错误日志来处理任何不规则的事情。这样您就可以清楚地访问被拒绝的数据以及数据库中拒绝的原因。
回答by Florin Ghita
Seems i was right. I count as good solution the Alex Poole's comment, the solution of Gary(recomended also by Tom kyte), and I found another trick in the ecuation with my coleagues:
看来我是对的。我认为 Alex Poole 的评论是很好的解决方案，Gary 的解决方案（也由 Tom kyte 推荐），我在与同事的交流中发现了另一个技巧：
To put OPTIONS(ROWS=100000000) - more than input data can have - and load conventionaly. (We will have only one commit or none) With this, we know that, if is loaded something, is loaded everything.
放置 OPTIONS(ROWS=100000000) - 超过输入数据可以拥有 - 并加载常规。（我们将只有一次提交或没有提交）有了这个，我们知道，如果加载了某些内容，则加载了所有内容。
