Stand-alone SQL statement vs. SQL statement inside PL/SQL block
I have a MERGE statement that runs perfectly - every time - as a stand-alone SQL statement. As soon as I wrap it in a BEGIN => END block, it freezes. I.E., it never errors out. I'm talking about both in SQL Developer and command-line SQL*Plus.
If I look at the session from another session (using SQL Developer "Monitor Sessions"), it has a wait event of "library cache load lock", 13835058056493658648,13835058056501857496,501.
Now - we have a scheduling tool called Control-M. Does what dbms_scheduler does, except it has a GUI interface, color coded, tracks performance metrics, sends out texts & e-mail, etc. When I run the PL/SQL block from that tool, it errors out after exactly 15 minutes. The error logged is "ORA-04021: timeout occurred while waiting for lock object.".