Oracle SQL Developer: stop script running if error

Question

The default behaviour of SQL Developer is to keep running the subsequent commands in a script even though an error is encountered. Is it possible to have it stop, or ask the user whether to continue or not (e.g. like in SQL Workbench)?

Answer 1

SQL Developer supports the SQL*Plus commands whenever sqlerror and whenever oserror.

These allow you to stop processing when the script hits an issue.

For example, if I run this:

select * from dual
where  ;

select * from dual;

whenever sqlerror exit
whenever oserror exit

select * from dual
where  ;

select * from dual;

The output is this (notice the final select doesn't happen):

SQL> select * from dual
  2  where  ;

Error starting at line : 2 in command -
select * from dual
where  
Error at Command Line : 3 Column : 7
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
SQL> 
SQL> select * from dual;
DUMMY   
X        


SQL> 
SQL> whenever sqlerror exit
SQL> whenever oserror exit
SQL> 
SQL> select * from dual
  2  where  ;

Error starting at line : 10 in command -
select * from dual
where  
Error at Command Line : 11 Column : 7
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action: