Oracle SQL Developer: run script, continue on error

Question

I'm using SQL Developer 20.2 to run batch scripts for Oracle 12c database updates. I drag&drop the script file into the editor window and then hit the button "Run Script (F5)". For some reason the program stops if the script runs into an error (DDL statements - no explicit throw of a PL/SQL function).

Example:

drop sequence my_seq;  -- sequence may not exist yet
create sequence my_seq
  start with 1
increment by 1
    minvalue 1
    maxvalue 9999999999
       cache 100
       cycle;

The drop statement may hit an error because the sequence does not exist. SQL*Plus ignores that error and continues to run the script but SQL Developer stops the execution resulting in an incomplete patch update.

What can I do?

Roberto Hernandez · Accepted Answer · 2020-09-02 12:18:43Z

5

SqlDeveloper by default stops when it finds an error. You can change that behaviour using this:

whenever sqlerror continue;
drop sequence my_seq;  -- sequence may not exist yet, in case of error continue
-- then we want to stop in error
whenever sqlerror exit failure;
create sequence my_seq
  start with 1
increment by 1
    minvalue 1
    maxvalue 9999999999
       cache 100
       cycle;

Example

Explanation

whenever sqlerror continue instructs to the script to continue no matter what error might happen
whenever sqlerror exit failure instructs to the script to stop and exit the execution in case of an error.

Reference to the command and options available

whenever sqlerror

edited Sep 2, 2020 at 12:18

answered Sep 2, 2020 at 11:50

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thatjeffsmith · Accepted Answer · 2020-09-02 12:32:29Z

0

That shouldn't be happening. By default SQL Developer runs the entire contents of the editor or whatever you have selected when you hit F5.

drop sequence my_seq;  

create sequence my_seq
  start with 1
increment by 1
    minvalue 1
    maxvalue 9999999999
       cache 100
       cycle;

F5

You can see that the drop fails, but it continues onto the CREATE. This was all in one 'go'

answered Sep 2, 2020 at 12:32

thatjeffsmith

22.7k6 gold badges44 silver badges139 bronze badges

2 Comments

Markus L Over a year ago

I believe that was true for older versions. That's why I developed the code like that and never had problems. Maybe there's also a setting. Roberto Hernandez's solution works.

thatjeffsmith Over a year ago

older, maybe decade ago...also maybe he has something in his login.sql that's setting whenever sqlerror exit, that's my best guess

Collectives™ on Stack Overflow

Oracle SQL Developer: run script, continue on error

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related