Error Handling in an Oracle Script

Question

I have been trying to figure this out for a little while now and I think it is time to ask for help.. I am building a schema provisioning script and I want to add some script output and error handling. The idea is that the script output window would only show me key messages without all the noise.

Create Temporary Error Table
Begin Transaction

-- begin work block
Print "Doing some types of work"
-- do work here

If Error and Active Transactions > 0 Then Rollback
If Active Transactions = 0 Then Insert Error In Temp Error Table and Start Another Transaction
-- end work block

-- once all all work complete
If Active Transactions > 0 Then Commit Transactions

In the SQL Server world I would normally just do this with Red Gate's SQL Packager which has it figured out (hint, hint Red Gate - we need an Oracle version :)). Any thoughts on where to begin with Oracle to get something similar?

Answer 1

In Oracle, you define the transaction boundaries -- you commit when you're done, and each statement is atomic.

If you're using SQL*Plus and you don't want anything at all to commit if anything goes wrong, you can do put the following in the SQL script:

SET ECHO ON
SPOOL /some/path/to/logfile.log
WHENEVER SQLERROR EXIT SQL.CODE ROLLBACK

-- run your code or DML statements

COMMIT;
EXIT;

This will bomb out the first time it encounters an error, so the erroring statement will be at the end of the log file. It will also rollback any changes, so as long as there aren't any COMMITs (or statements that cause them, like CREATE, ALTER, DROP, GRANT, or REVOKE), then entire upgrade is all-or-nothing.