I don't know whether this is the right place or not, but there is a definite lack of Control-M forum (anywhere) so I picked here.
I have taken over a system sheduled by control-m and the wrapper script that calls oracle procedures is not returning error codes etc back to control-m.
The error is definately raised because it writes the error to a plog before running RAISE_APPLICATION_ERROR (-20001,'Bla Bla Bla');
Sysout from control-m looks like:
+ /opt/ads/uat/scripts/ctm2ora.sh uat CDRUAT CDR.CDR_CLAIM_AGEING_PKG.PROCESS_BY_SUBMISSION -p success -i 97464
Version 1.1
uat CDRUAT CDR.CDR_CLAIM_AGEING_PKG.PROCESS_BY_SUBMISSION -p success -i 97464
set uat_SUCCESS to
storedprogargs='97464'
usrlogintxt= /
Nothing to do
Result: Failure.
Here is the script ctm2ora.sh
Any help would be greatly appreciated
Thanks
Kenton
(Note: I must remember to add something witty & technical for my signature)
I have taken over a system sheduled by control-m and the wrapper script that calls oracle procedures is not returning error codes etc back to control-m.
The error is definately raised because it writes the error to a plog before running RAISE_APPLICATION_ERROR (-20001,'Bla Bla Bla');
Sysout from control-m looks like:
+ /opt/ads/uat/scripts/ctm2ora.sh uat CDRUAT CDR.CDR_CLAIM_AGEING_PKG.PROCESS_BY_SUBMISSION -p success -i 97464
Version 1.1
uat CDRUAT CDR.CDR_CLAIM_AGEING_PKG.PROCESS_BY_SUBMISSION -p success -i 97464
set uat_SUCCESS to
storedprogargs='97464'
usrlogintxt= /
Nothing to do
Result: Failure.
Here is the script ctm2ora.sh
Code:
#!/usr/bin/ksh
#
###############################
#Comment Block
#
#Author Date Change
#
#
###############################
#
#set -x
echo "Version 1.1"
echo $*;
runenv=$1
failmsg="Result: Failure."
successmsg="Result: Success."
#####
# Oracle Environment changes 1.1
export PATH=/usr/local/bin:$PATH
export ORACLE_SID=$2
ORAENV_ASK=NO . /usr/local/bin/oraenv
#####
if [ x$runenv = "x" ]; then
echo "The run environment parameter was not set. This must be set in parameter 1"
echo $failmsg
exit 1
fi
if [ -z "$CONTROLM" ]; then
echo "Control M path in environment variable \$CONTROLM is not set in \
.profile"
echo $failmsg
exit 1
fi
if [ ! -d "$CONTROLM/exe" ]; then
echo "Control M executables directory $CONTROLM/exe is not valid."
echo $failmsg
exit 1
fi
if [ ! -f "$CONTROLM/exe/ctmvar" ]; then
echo "Control M executable file $CONTROLM/exe/ctmvar not found."
echo $failmsg
exit 1
fi
if [ ! -x "$CONTROLM/exe/ctmvar" ]; then
echo "Control M executable file $CONTROLM/exe/ctmvar is not executable."
echo $failmsg
exit 1
fi
#if [ -z "$ORACLE_SID" ]; then
# echo "Oracle environment variable \$ORACLE_SID is not set."
# echo $failmsg
# exit 1
#fi
if [ -z "$ORACLE_HOME" ]; then
echo "Oracle environment variable \$ORACLE_HOME is not set."
echo $failmsg
exit 1
fi
if [ ! -d "$ORACLE_HOME" ]; then
echo "Oracle environment variable \$ORACLE_HOME $ORACLE_HOME is not a directory."
echo $failmsg
exit 1
fi
if [ ! -d "$ORACLE_HOME/bin" ]; then
echo "Oracle directory \$ORACLE_HOME/bin $ORACLE_HOME/bin is not valid."
echo $failmsg
exit 1
fi
if [ ! -f "$ORACLE_HOME/bin/sqlplus" ]; then
echo "$ORACLE_HOME/bin/sqlplus does not exist."
echo $failmsg
exit 1
fi
if [ ! -x "$ORACLE_HOME/bin/sqlplus" ]; then
echo "$ORACLE_HOME/bin/sqlplus is not executable."
echo $failmsg
exit 1
fi
# start by setting the ctrlm SUCCESS variable to space (not successful)
eval "$CONTROLM/exe/ctmvar -action set -var %%\\\\${runenv}_SUCCESS \
-varexpr ' ' >/dev/null 2>&1"
if [ $? -ne 0 ]; then
errtxt="ERROR: ctmvar returned an error. Output is :"
for i in `eval "$CONTROLM/exe/ctmvar -action set -var \
%%\\\\${runenv}_SUCCESS -varexpr ' '"`; do
errtxt=$errtxt" "$i
done
echo $errtxt
echo $failmsg
exit 1
fi
echo "set ${runenv}_SUCCESS to"
if [ $# -ge 3 ];
then
export ORACLE_SID=`echo $2 |tr '[a-z]' '[A-Z]'`
#
# check if an explicit switch where the parameters start in the arg list.
# If not, then assume params start from the arg with the "SUCCESS" value.
#
storedprog=$3
# Check for input values to the sded for the stored procedure
comma=
storedprogargs=
for i in `echo $* | \
awk 'BEGIN { argsfound=0; }\
{ for (i = 1; i<=NF; i++) {
if (toupper($i)=="\-P") {
if (argsfound==1) {
exit 0;
}
}
if (argsfound==1) {
print $i;
}
if (toupper($i)=="\-I") {
argsfound=1;
}
}}'`; do
storedprogargs=`echo ${storedprogargs}${comma}"'"${i}"'"`
comma=","
done
echo "storedprogargs="$storedprogargs
# Check for param values to be provided to ctrlM
paramswitch=`echo $* | grep -ic " \-p "`
# echo "paramswitch="$paramswitch
if [ $paramswitch -eq 0 ]; then
integer parampos=`echo $* | \
awk '{ for (i =1; i<=NF; i++)
if (toupper($i)=="SUCCESS") {
print i; exit 0;} }'`
else
integer parampos=`echo $* | \
awk '{ for (i =1; i<=NF; i++)
if (toupper($i)=="\-P") {
print i; exit 0;} }'`
if [ $parampos -ne 0 ]; then
let parampos+=1
fi
fi
# echo "parampos="$parampos
if [ $parampos -eq 0 ]; then
echo "ERROR: no identifiable parameters were provided."
echo $failmsg
exit 1
fi
sqlvals=
integer valsfound=0
# tstpasswd is set up for testing only. Set it in the environment of the
# test user.
if [ x$tstpasswd = "x" ]; then # no testing password set in the environment
usrlogintxt=" / "
else
usrlogintxt="dsuser@${ORACLE_SID}/$tstpasswd"
fi
echo "usrlogintxt="$usrlogintxt
sqlplusout=
for fieldval in `$ORACLE_HOME/bin/sqlplus -l -s $usrlogintxt <<-EOF
set feed off;
set serveroutput on;
set heading off;
exec $3($storedprogargs);
exit
EOF`; do
sqlplusout=$sqlplusout" "$fieldval
if [ x$fieldval = "x" ]; then
continue;
fi
if [ $valsfound -eq 0 ]; then
if [ x$fieldval != "xSUCCESS" ]; then
# skip sqlplus junk output
continue;
else
valsfound=1
fi
fi
sqlvals=${sqlvals}$fieldval
done;
# This code block causes an error to be raised when a proc doesn't
# write the value xSUCCESS using DBMS_OUTPUT. This script currently
# is being used to call procs which don't write the value xSUCCESS
# using DBMS_SOUTPUT and so causes erroneous failures.
#Kenton adding the next 6 lines back in
if [ $valsfound -eq 0 -a ! -z "$sqlplusout" ]; then
sqlplusout=`echo $sqlplusout | sed -e 's/.*ORA/ORA/'`
echo "sqlplus failed maybe. Output=:"$sqlplusout
echo $failmsg
exit 1
fi
unset usrlogintxt
if [ -z "$sqlvals" ]; then
# nothing to do
echo "Nothing to do"
echo $failmsg
exit 0
fi
#
# Example of output from the previous sqlplus:
# echo $sqlvals
# SUCCESS=10093===EXTR=EXTRACT=EXTR
# Delimiter is =, so to make the following for loop work,
# put a carat in every null cell i.e. == -> =^= . This allows the correct
# allignment of the variable names in the argument list to the values from
# the sqlplus. So, with the above example we derive:
# SUCCESS=10093=^=^=EXTR=EXTRACT=EXTR
#
# or another example:
# =10093===EXTR=EXTRACT=
# becomes
# ^=10093=^=^=EXTR=EXTRACT=^
sqlvals=`echo $sqlvals| \
sed -e 's/==/=^=/g' -e 's/==/=^=/g' -e 's/^=/^=/' -e 's/=$/=^/'`
for fieldval in \
`echo $sqlvals | awk -F'=' '{ for (i = 1; i<=NF; i++) print $i }'`; do
if [ $# -lt $parampos ]; then
# there are more values than there are variables
echo "ERROR: There are more values returned by $storedprog than "\
"there are variable arguments provided to ${0}."
echo $failmsg
exit 1
fi
eval "variable=\$${parampos}"
if [ x$fieldval = "xSUCCESS" ]; then
# skip success for the moment
shift
continue
fi
tstvar=`echo $variable |tr '[a-z]' '[A-Z]'`
if [ "$tstvar" = "-I" ]; then
# found sqlplus input params start
break
fi
if [ x$fieldval = "x^" ]; then
fieldval=" "
fi
echo "set "$variable" to "$fieldval
eval "$CONTROLM/exe/ctmvar -action set -var %%\\\\$variable \
-varexpr '$fieldval' >/dev/null 2>&1"
if [ $? -ne 0 ]; then
errtxt="ERROR: ctmvar returned an error. Output is :"
for i in `eval "$CONTROLM/exe/ctmvar -action set -var \
%%\\\\$variable -varexpr '$fieldval'"`; do
errtxt=$errtxt" "$i
done
echo $errtxt
echo $failmsg
exit 1
fi
shift
done
# Now are there any args left over that did not get values? Add them to ctlm
# with blank values for the moment. Maybe a crash is more appropriate?
while [ $parampos -le $# ]; do
eval "variable=\$${parampos}"
tstvar=`echo $variable |tr '[a-z]' '[A-Z]'`
if [ "$tstvar" = "-I" ]; then
# found sqlplus input params start
break
fi
echo "set $variable to space"
eval "$CONTROLM/exe/ctmvar -action set -var %%\\\\$variable \
-varexpr ' ' >/dev/null 2>&1"
if [ $? -ne 0 ]; then
errtxt="ERROR: ctmvar returned an error. Output is :"
for i in `eval "$CONTROLM/exe/ctmvar -action set -var \
%%\\\\$variable -varexpr ' '"`; do
errtxt=$errtxt" "$i
done
echo $errtxt
echo $failmsg
exit 1
fi
shift
done
else
echo usage : $0 ORACLESID PACKAGE.PROC CONTROLMVARNAME1 [CONTROLMVARNAMEn..]
echo example: $0 CDRDEV cdr.submission.next_job test1 test2 test3 test4 test5
echo $failmsg
exit 1
fi
eval "$CONTROLM/exe/ctmvar -action set -var %%\\\\${runenv}_SUCCESS \
-varexpr 'SUCCESS' >/dev/null 2>&1"
if [ $? -ne 0 ]; then
errtxt="ERROR: ctmvar returned an error. Output is :"
for i in `eval "$CONTROLM/exe/ctmvar -action set -var \
%%\\\\SUCCESS -varexpr 'SUCCESS'"`; do
errtxt=$errtxt" "$i
done
echo $errtxt
echo $failmsg
exit 1
fi
echo "set ${runenv}_SUCCESS to SUCCESS"
echo $successmsg
exit 0Any help would be greatly appreciated
Thanks
Kenton
(Note: I must remember to add something witty & technical for my signature)
