Oracle Database Discussions

Hi Gurus,

I need your help. I have created a shell script that calls a procedure and creates a file. While compiling this shell script, I am getting the following error:

/d01/app/oracle/oadev/oadevappl/lfnd/11.5.0/bin

LOGI_NOTIFMAIL_SH[92]: sqlplus: not found
Return-

------------------------------------------------------------------------------------------------------------

Contents of my shell script are as given below:
----------------------------------------------------------------

{
# Get the 2nd word from the string, This is the login
FCP_REQID=`echo $@ | awk '{split($0,myarray);print myarray[2];}'`
eval $FCP_REQID
echo $FCP_REQID

# Get the 3rd word from the string, This is the login
FCP_LOGIN=`echo $@ | awk '{split($0,myarray);print myarray[3];}'`
eval $FCP_LOGIN

# Get the 4th word from the string, This is the user_id
FCP_USERID=`echo $@ | awk '{split($0,myarray);print myarray[4];}'`
eval $FCP_USERID
echo $FCP_USERID

# Get the 9th word from the string, the 1st parameter
vregion=`echo $@ | awk '{split($0,myarray);print myarray[9];}'`;
echo $vregion

# Get the 10th word from the string, the 2nd parameter
vmodule=`echo $@ | awk '{split($0,myarray);print myarray[10];}'`;
echo $vmodule

# Get the 11th word from the string, the 3rd parameter
vnotification=`echo $@ | awk '{split($0,myarray);print myarray[11];}'`;
echo $vnotification

# Get the 12th word from the string, the 3rd parameter
vemail=`echo $@ | awk '{split($0,myarray);print myarray[9];}'`;
echo $vemail

vDate=`date '+%d-%b-%Y'`
DATAHOMEDIR="/d01/app/oracle/oadev/oadevappl/lfnd/11.5.0/bin"
echo $DATAHOMEDIR
# Clean data directory
/bin/rm -f $DATAHOMEDIR/*User_Access*.csv

echo $FCP_LOGIN
vRVAL="`sqlplus -s <<EOF
$FCP_LOGIN
WHENEVER SQLERROR EXIT FAILURE
SET FEED OFF
SET HEAD OFF
SET TERM OFF
SET SERVEROUTPUT ON SIZE 1000000
SET TIMING OFF
DECLARE
vRegion:=$vregion;
vModule:=$vmodule
vNotification:=$vnotification;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(
user_id => 0,
resp_id => 59274,
resp_appl_id => 20006,
security_group_id => 0
);
LFND_USER_ACCESS_REP_PKG.MAIN_PRC(vRegion,vModule,vNotification);
IF SQL%ROWCOUNT>0 THEN
DBMS_OUTPUT.PUT_LINE('SUCCESS');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FAILED'||sqlerrm);
END;
/
EXIT
EOF
`"
echo "Return- $vRVAL"
vSUCCESS=`echo $vRVAL | awk '{split($0,myarray);print myarray[1];}'`
#vCNT=`echo $vRVAL | awk '{split($0,myarray);print myarray[2];}'`

if [ " $vSUCCESS" == " SUCCESS" ]; then
# Code to send E-Mail if E-Mail Parameter is not null
if test -n $vemail
then
echo " This Mail contains information about " >> test_body${FCP_REQID}.txt
echo " Please see the attched file for detail " >> test_body${FCP_REQID}.txt

export MAILTO="${vemail}"
export CONTENT="test_body${FCP_REQID}.txt"
export SUBJECT="LOGI User Access Approval Listing Report"
export MAILFROM="Alert_mailer.logitech.com"
export ATT="$DATAHOMEDIR/$vregion_$vmodule_User_Access_$vDate.csv"
BOUNDARY='=== This is the boundary between parts of the message. ==='

{
print - "From: <$MAILFROM>"
print - "To: <${MAILTO}>"
print - 'Subject:' $SUBJECT
print - 'MIME-Version: 1.0'
print - 'Content-Type: MULTIPART/MIXED; '
print - ' BOUNDARY='\"$BOUNDARY\"
print -
print - ' This message is in MIME format. But if you can see this,'
print - " you aren't using a MIME aware mail program. You shouldn't "
print - ' have too many problems because this message is entirely in'
print - ' ASCII and is designed to be somewhat readable with old '
print - ' mail software.'
print -
print - "--${BOUNDARY}"
print - 'Content-Type: TEXT/PLAIN; charset=US-ASCII'
print -
print -
print -
cat ${CONTENT}
print - "--${BOUNDARY}"
print - 'Content-Type: TEXT/PLAIN; charset=US-ASCII; name='${ATT}
print - 'Content-Disposition: attachment; filename='${ATT}
print -
cat ${ATT}
print -
print - "--${BOUNDARY}--"
} | /usr/sbin/sendmail ${MAILTO}
rm test_body${FCP_REQID}.txt
else
REP_OUT " No need to send an E mail becasue E mail Parameter is null"
fi
fi

}


Thanks & Regards,
Sumir Chawla