SQLPlus command line script hangs when inserting records

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 i开发者_运维知识库s 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;

---

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.

---

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.