OraFAQ Forum: SQL & PL/SQL » Error Handling in SQL Scripts

Home » SQL & PL/SQL » SQL & PL/SQL » Error Handling in SQL Scripts

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error Handling in SQL Scripts [message #23434]

Mon, 09 December 2002 08:18

AA
Messages: 11
Registered: December 2001

Junior Member

I have the following script

======================
rem this script drops a synonym $1 and creates the same synonym on table $2

drop synonym &1;
create synonym &1 for &2;
commit;

exit;
=====================
Is there a way I can exit with a return code or something when there is a failure in the drop or create statements ?

Thanks !

Report message to a moderator

Re: Error Handling in SQL Scripts [message #23435 is a reply to message #23434]

Mon, 09 December 2002 09:31

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can use something like this( options are to exit
on an SQL error or on an OSerror)

sql>whenever < sqlerror / oserror > exit
sql>-- your sql statment --

Report message to a moderator

Re: Error Handling in SQL Scripts [message #23440 is a reply to message #23434]

Mon, 09 December 2002 12:35

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Mahesh is right, but to make it more clear, you can change the error handling to turn it on and off as follows:

whenever sqlerror exit 1
drop synonym &1;
whenever sqlerror continue
create synonym &1 for &2;
etc.

Commit is not required for ddl statements. Best not to put it in unless there is something to commit. Every command should be there for a reason...

Report message to a moderator

Previous Topic:	How to select recusrsively in an sql statement
Next Topic:	Can we store Image in oracle database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 05:42:06 CDT 2024