Geek Answers Handbook

Error Handling in Oracle Script

I’ve been trying to figure this out for a while, and I think it's time to ask for help. I am creating a schema for a script, and I want to add script output and error handling. The idea is that the script output window will only show key messages without any 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 usually did this using the Red Gate SQL Packager , which he found out (hint, Red Gate hint - we need the Oracle version :)). Any thoughts on where to start with Oracle to get something like this?

+5
source share
2 answers

Oracle - , , .

SQL * Plus, , - , 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;

, . , COMMIT ( , , CREATE, ALTER, DROP, GRANT REVOKE), " ".

+2

, , () .

- UNIX script, .sql .log. , - .

- , sqlplus .

...

.ksh

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

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

echo "end of provisioning script"

file1.sql( )

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

script, , - .. .. ..

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

script .. .. . sqlplus, ..

, . ,

, ...

0

More articles:


All Articles