Stopping Oracle SQL*Plus Scripts When They Go Wrong

Consider the following Oracle SQL*Plus script:

create table newtable as select 'NewValue' new_column, oldtable.* from oldtable;
drop table oldtable;
rename newtable to oldtable;

Seems reasonable? What happens if oldtable is a large table containing lots of precious data? You do not want to loose oldtable if newtable has not been created properly. And if it is a large table that you are copying the probability of something going wrong with the initial "create" statement is increased (blowing rollback segments, running out of space, block corruption, someone having already created an empty oldtable).

Of course, these errors never happen - testing is always perfect, capacity planning is always spot on, and no idiot is let loose on the database creating tables - except in the real world.

We could split the script into two - only dropping and renaming the table once we had seen the creation took place successfully. But if we were doing this with one hundred tables, the editing and checking would soon get tedious.

The PeopleSoft people amongst you may have noticed that the above script is a simplification of the method PeopleSoft Application Designer uses to alter tables by recreation.

There is a solution! Just simply add to the top of your script:

whenever sqlerror exit sql.sqlcode rollback

Oracle SQL Plus will then exit if an error occurs. This is not wholly satisfactory as you do not know what went wrong. You therefore need to spool to a file so that you can check:

spool logfile

Note that this is not the same the SQL command whenever.

Summary

This can be added to each script, or create a script with it in to run before each script, or you can add this to a login.sql so that these commands are executed. You need to then explicitly turn these options off you do not need them, and using login.sql implies the use of a particular starting directory, so you could have a directory dedicated to this type of script. In the case of PeopleSoft any script generated by PeopleTools.

Computing Articles