Hi All,

Now i am running the 3 oracle procedures one by one manually.

Query: If 1st Procedure OUT_PUT is Success, then call 2nd Procedure.
If 2nd Procedure OUT_PUT is Success, then call 3rd Procedure.

If 1st Procedure is failed, then no need of calling the other
procedures.

How to Schedule 1st, 2nd, 3rd Procedures jobs are@
###############################################
SCHEDULE JOBS
###############################################
# 30 01 * * * /u14/ods/scripts/in_pps_state_change.sh >> /u14/ods/scripts/scriptslog/in_other_mail.log 2>&1

# 00 03 * * * /u14/ods/scripts/in_pps_cos_change.sh >> /u14/ods/scripts/scriptslog/in_other_mail.log 2>&1

# 30 04 * * * /u14/ods/scripts/in_pps_usage_change.sh >> /u14/ods/scripts/scriptslog/in_other_mail.log 2>&1


################################################
CALLING ORACLE PROCEDURE FROM SHELL SCRIPT
################################################
Eg of SP calling:: in_pps_usage_change.sh

RETVAL=`sqlplus -s ODS/ODS@ODSDB <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare
OUT_STATUS NUMBER;
OUT_MSG VARCHAR2(200);
Begin
ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG);
dbms_output.put_line ('KeepThis '||OUT_STATUS ||' '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`

echo " " >> $USER_LOG
echo "Procedure: ODS_SP_REMOVE_PRE_SUB_DUP output is: " >> $USER_LOG
echo "OUT_STATUS= $X" >> $USER_LOG
echo "OUT_MSG= $Y " >> $USER_LOG

################################################


Please provide me the solution of my query..

Thanks&Regards
Hanuma

Why don't you keep them all in a single script, and start that at 01:30? Then it's much easier to see if the first subjob failed.

You could even run them with "at" if running them at a particular time is important. But then, you need to be sure that in_pps_state_change.sh finishes before the next job is due.

The actual shell script hurts to look at, but I guess you weren't soliciting criticisms for that (-:

Thank you....

I have one more dought??

If the 1st procedure is executed, but process is not done due to ORA- 03113 : End of Comminication channel Error.

In this case IF LOOP can run the 2nd & 3rd Procedures successfully ?????

Any solution is there, to control 2nd, 3rd procedures???

You should also take care to set the exit code properly from the script, so that the shell script which collects these can examine it.

I guess something like if OUT_STATUS is "Success" exit 0, otherwise some higher number (0 means success).

You are in control of the first shell script (and if not, create a wrapper script which you are in control of), so simply make it return success (zero) under the conditions when the second and third scripts can be run, and non-zero if not.

O.K,

Thanking you....