My question if it's possible to avoid the execution of a DDL operation in an SQL script if the previous operation returns an Error.

SQL script

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
MERGE finalTABLE using tempTABLE;
DROP tempTABLE;

COMMIT;

EXIT;

The first line (WHENEVER SQLERROR...) prevents from execute the commit and operations if something went wrong in script execution. But in this case the DDL operation DROP is being executed. And in this particular case we DROP the table and content even if the merge/transfer returns an error.

Is there any way to abort DROP execution if MERGE goes wrong.

A simple ERROR: Parent data not found make our day.

The whenever line is defined before the spool command to log returns from sqlplus tool.

logfile for execution:

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected.
[RS2|rs2]SQL>[RS2|rs2]SQL>


3647465 rows merged.


Table dropped.


Commit complete.

18229970 rows merged.

Table dropped.


Commit complete.


MERGE INTO RS2 val using (SELECT * FROM RS2_TMP) tmp ON (tmp.id = val.id)
*
ERROR at line 1:
ORA-02291: integrity constraint (RS2.RS2VAL3) violated - parent key not found

Table dropped.


Commit complete.