One big hint you are seeing (upon the initial execution) is the failure to display the 'select sysdate from dual' output. On the second execution, however, you do see it.
So what's going on here? If you specify EXIT in a login.sql script used by a sqlplus or sqlcl session, then your test case will actually disconnect the session, so of course the select sysdate never runs. SQL Developer probably works the same way, but with this extra confusion (which I suppose many would count as a bug):
1) No disconnect message appears in the Script Output and the connection icons (in the tree and the worksheet drop-down list) still indicate the connection is active.
2) Immediately checking View -> Log -> Statements proves 'select sysdate' did not run.
3) If you Run Script (F5) again, SQL Developer automatically reconnects, but without running the login.sql script again.
Anyway, you could log a SR through MOS and wait for a bug fix, but it is probably better to adapt to the current way the product works. Avoid
whenever sqlerror exit rollback;
whenever sqlerror continue rollback;
Thanks for replying.
Apart from EXIT, Is there a way to stop the execution when there is an error?
The method you proposed (whenever sqlerror continue rollback;) would still execute the following statements in case of an error.
No. Wanting to exit the session upon hitting an error is perfectly reasonable, and of course SQL Developer does exactly that if "whenever sqlerror exit rollback;" is in your login.sql script.
My preference, however, would be not to have to deal with this confusing behavior (no disconnect message in the results tab, and the automatic reconnect bit without running the login.sql again). I would probably not remember that EXIT on error condition in my login script.
I would much prefer to run SQL Developer with no login.sql, or one (specified in Preferences -> Database, it does not have to be called login.sql) without that EXIT and develop and test my code interactively in a worksheet. Once I get things working there, I would create a script for sqlplus or sqlcl with appropriate output spooling and let those tools use a login.sql with the "whenever sqlerror exit rollback;".
My two cents, Cheers