插入记录时 SQLPlus 命令行脚本挂起
在Windows上使用sqlplus 11g在11g数据库上运行sql脚本。如果插入行留在脚本中，Sqlplus 将挂起。如果我注释掉插入行，脚本将返回记录。
脚本执行时间<在 sql Developer 中运行时为 1 秒。
我试图使插入语句成为具有相同结果的连续行。
使用 echo on，它会在 sql 最后一行（...is null;）之后挂起，并显示一个新行开始，提交永远不会被处理。我假设我的插入语句语法不正确，无法在 sqlplus
命令行调用中运行：
sqlplus e_fraud/e_fraud@eftst2 @"C:\temp\Actimize_ETL\Versions\03\sql\merge2000_insert_new_bank_keys.sql"
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;
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.
Script exec time is < 1 sec when run in sql developer.
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
Command line call:
sqlplus e_fraud/e_fraud@eftst2 @"C:\temp\Actimize_ETL\Versions\03\sql\merge2000_insert_new_bank_keys.sql"
sql script:
--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;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果语法错误，它会告诉你，而不是挂起。
目前尚不清楚您在 SQL*Plus 中尝试之前或之后是否在 SQL Developer 中成功运行了它。我的猜测是，您首先在那里运行插入，并且没有提交或回滚该事务，从而导致锁定 - 这表明 e_fraud 列之一可能存在唯一约束。或者可能不太可能的是，您已在一个会话中插入并提交，但随后在另一个会话中您在重新运行之前删除了记录，但未提交。
您的 SQL*Plus 会话正在等待其他事务以任何方式完成，因此它可以执行插入或报告约束违规，但目前处于不确定状态。确保您已在打开的每个会话中提交或回滚。如有必要，有多种方法可以检查锁。
顺便说一句，由于 Oracle 对 null 和空字符串的处理方式相同，因此您不需要任何
!= ''子句。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 not committed.
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.
Incidentally, as null and the empty string are treated the same by Oracle, you don't need either of the
!= ''clauses.我相信bank_key 中的“或”逻辑不为空，或者bank_key != '' 最终基本上通过混淆较低级别的程序集来返回所有内容。因此，当它到达为空的bank_key 时，它将尝试插入并挂起。
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.