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 most of the cases I have seen, such (repeating) tasks are automated using scripts.

One way we do it currently is a UNIX script that runs all the .sql files in a given directory and generates a .log file. At the end of the process, we grep the log file and see if there are any errors.

You wont need to print any errors manually, because sqlplus already prints out the error and it is captured in the log file.

Below is a very simple example...

.ksh file

#!/usr/bin/ksh
echo "Starting provisioning script.."

sqlplus scott/tiger@oracle102 > file1.log << !
@file1.sql
@file1.sql
!

echo "end of provisioning script"

And the contents of file1.sql (which is in the same directory for this example)

create table test123(
   id number,
   name varchar2(200)
);

when i run this script, the first create succeeds and the second one fails..and the log file would be .. like..

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 6 20:44:08 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
Table created.

SQL> create table test123(
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

You can prepare a script along these lines.. and then look for any errors in the log file.. once the execution is complete. You could use various sqlplus session commands t get rid of all the unwanted comments and such.

I am not aware of any automated tools that do the same. The reason I've always

Hope this helps...

Answer 2

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.