| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: writing job dependencies shell script
Hi Elain,
Some people have already responded with options to use in shell scripts but I just wanted to throw in the possibility of using PL/SQL to wrap things together - presenting what might be a neater solution.
I frequently have to write scripts that must be safe for someone to execute in the middle of the night. Therefore the scripts have to be smart enough to restart automatically if they fail half-way through and not do anything stupid if run twice (and ideally not waste time re-executing something that was successful). I tend to use PL/SQL - running queries to determine current state and then if/then/else statements to report the decision made. Outputing the state about to be entered (with a timestamp) also helps if the command fails. When working with DDL I use the tables like all_tables, all_ind_columns, etc) to determine whether or not commands are required and then wrap the DDL in EXECUTE IMMEDIATE statements.
Regards,
Mark.
"elain he"
<elainhe_at_hotmail.c To: oracle-l_at_freelists.org
om> cc:
Sent by: Subject: writing job dependencies shell script
oracle-l-bounce_at_fr
eelists.org
19/02/2004 14:50
Please respond to
oracle-l
Hi,
I'm trying to figure out how I can write a shell script that checks the
success or failure of DML statements. I have a number of jobs that are
dependent on one another. For eg. if JobA fails do not process jobB, and
likewise if jobB fails, do not process jobC.
Each job looks like the following:
jobA:
===
#!/bin/ksh
sqlplus << eof
username/password
update tableA set .....
eof
jobB:
I'm trying to write a simple shell script that does the following checks
before executing each job:
#!/bin/ksh
jobA
if [ $? = 0 ];then
jobB
if [ $? = 0];then
jobC
if [ $? = 0];then
jobD
else
exit
fi
else
exit
fi
else
exit
The problem I'm encountering is $? always returns 0 because the sqlplus always completes successfully even though my update or delete statement fails due to rollback seg space or any other issues.
Can someone provide me some pointers on how I can write an error handling routine? If there is a better way of writing this, please let me know.
thanks.
elain
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |