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?

I am building a schema provisioning script If you mean you are creating/altering tables and such; rollback won't help. Within Oracle, a DDL command commits the current transaction, if one is outstanding, runs itself within a transaction, and commits on success, rolls back on failure. Thus after running a DDL command there will be no outstanding transaction. The only exception being if there is a parse error, then the DB doesn't know that it was supposed to run a DDL command, and won't do the first implicit commit, nor start a transaction for the DDL. This is very different from SQL Server. — Shannon Severance
– Shannon Severance, Commented Aug 7, 2010 at 4:49
My comment doesn't provide an answer, it's only there to point out a pitfall that you may or may not have been aware of. — Shannon Severance
– Shannon Severance, Commented Aug 7, 2010 at 4:51
@Shannon - Thanks, that really sucks that you can't do a broader scoped transaction that includes DDL. It really makes error handling much more difficult in the provisioning process by leaving the database in a half-baked state if something goes wrong. — Colin Bowern
– Colin Bowern, Commented Aug 9, 2010 at 14:33
:) To answer your hint, we don't have a Packager product for Oracle just yet, but you could try Schema Compare for Oracle and Data Compare for Oracle: red-gate.com/products/schema_compare_for_oracle/index.htm — David Atkinson
– David Atkinson, Commented Oct 26, 2010 at 13:57

Adam Musch · Accepted Answer · 2010-08-06 20:52:18Z

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.

answered Aug 6, 2010 at 20:52

Adam Musch

13.7k3 gold badges30 silver badges33 bronze badges

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

Comments

Rajesh Chamarthi · Accepted Answer · 2010-08-07 13:21:16Z

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...

I'm trying to stay away from mixing shell scripts if possible. Creates too many dependencies.

Collectives™ on Stack Overflow

Error Handling in an Oracle Script

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related