oracle 插入记录时 SQLPlus 命令行脚本挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3591277/
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
SQLPlus command line script hangs when inserting records
提问by longday
Using sqlplus 11g on windows to run sql script on 11g database. Sqlplus hangs if insert line left in script. If I comment out the insert line, the script returns the records.
在 windows 上使用 sqlplus 11g 在 11g 数据库上运行 sql 脚本。如果插入行留在脚本中，Sqlplus 会挂起。如果我注释掉插入行，脚本将返回记录。
Script exec time is < 1 sec when run in sql developer.
在 sql developer 中运行时，脚本执行时间 < 1 秒。
I have tried to make the insert statment one continuous line with same resutls.
我试图使插入语句成为具有相同结果的连续行。
using echo on, it hangs right after the sql last line (...is null;) and shows a new line starting, commit is never processed. I assume my insert statement is not syntaxed correctly to run in sqlplus
使用 echo on，它在 sql 最后一行之后挂起（...为空；）并显示一个新行开始，提交从未被处理。我假设我的插入语句的语法不正确，无法在 sqlplus 中运行
Command line call:
命令行调用：
sqlplus e_fraud/e_fraud@eftst2 @"C:\temp\Actimize_ETL\Versions\sql\merge2000_insert_new_bank_keys.sql"
sql script:
sql脚本：
--insert into e_fraud.bank
select stg.bank_key bank_key, stg.bank_key bank_name from
(
select distinct account_bank bank_key
from e_fraud.rgbk_stg_account
where account_bank is not null
or account_bank != ''
UNION
select distinct bank_key
from e_fraud.rgbk_stg_branch
where bank_key is not null
or bank_key != ''
) stg
left outer join e_fraud.bank b
on stg.bank_key = b.bank_key
where b.bank_key is null;
commit;
exit;
回答by Alex Poole
If the syntax was wrong it would tell you that, not hang.
如果语法错误，它会告诉您，而不是挂起。
It isn't clear if you ran it successfully in SQL Developer before or after trying it from SQL*Plus. My guess would be that you ran the insert there first, and haven't committed or rolled back that transaction, causing a lock - suggesting there might be a unique constraint on one of the e_fraud columns. Or maybe less likely, you've inserted and committed in one session, but then in another session you've deleted the records prior to rerunning but notcommitted.
不清楚在从 SQL*Plus 尝试之前或之后在 SQL Developer 中是否成功运行了它。我的猜测是您首先在那里运行插入，并且没有提交或回滚该事务，从而导致锁定 - 表明 e_fraud 列之一可能存在唯一约束。或者可能不太可能，您已经在一个会话中插入并提交，但随后在另一个会话中您在重新运行之前删除了记录但未提交。
Your SQL*Plus session is waiting for some other transaction to be finalised either way, so it can either perform the insert or report the constraint violation, but it's in limbo at the moment. Make sure you've committed or rolled back in every session you have open. There are ways to check for locks if necessary.
您的 SQL*Plus 会话正在等待某些其他事务以任何方式完成，因此它可以执行插入或报告约束违规，但目前处于不确定状态。确保您已在打开的每个会话中提交或回滚。如有必要，有一些方法可以检查锁定。
Incidentally, as null and the empty string are treated the same by Oracle, you don't need either of the != ''clauses.
顺便提一下，由于 Oracle 对 null 和空字符串的处理方式相同，因此您不需要任何一个!= ''子句。
回答by Jim Wells
I believe that the "or" logic in bank_key is not null or bank_key != '' ends up basically saying return everything by basically confusing the lower level assembly. So when it gets to a bank_key that is null it will try to insert and hang.
我相信 bank_key 中的“或”逻辑不是 null 或 bank_key != '' 最终基本上通过混淆低级程序集来基本上说返回所有内容。因此，当它到达为空的 bank_key 时，它将尝试插入并挂起。
