CODEWITHSUNDEEP
databaseshellemail-attachmentshostoracle-ebs
ARGStackOvaFlo
ARGStackOvaFlo

Reputation: 305

Send concurrent program XLS output as attachment through mail to target Email ID

I'm using Oracle EBS R12.1.3 on IBM AIX 5.3

Recently I got a requirement to send the output of a Concurrent program as an attachment through mail. The output is in .XLS format.

The below code works well for sending Concurrent program output file in XLS format to desired email ID.

echo "starting the program"
echo "################################"
echo "Current Child Request ID :"$4
REQ_ID=$4
echo $REQ_ID
echo "Do you want the output through auto-generated email:" $5

if [ $5 == 'Y' ]
then
echo "As per your request generating email containing XLS output as attachment for the Concurrent request $4"
YEAH_FILE=
sqlplus -s <DB_Schema_Name>/<Schema_Password>@<Instance_Name> << EOF
set linesize 300;
set pagesize 0;
set echo off;
set serveroutput off;
set sqlblank off;
set feedback off;
set heading off;
set wrap off;
col "REQ" format 9999999;
select '$APPLCSF/out/*' || REQUEST_ID ||'*.xls ' from FND_CONC_REQ_SUMMARY_V where REQUEST_ID ='$REQ_ID'; spool off EOF

#Get the email ID
SEND_MAIL_ID = 'echo <Email_ID>'

cat <<'EOF' - $YEAH_FILE | /usr/sbin/sendmail $SEND_MAIL_ID
Subject: Email with XLS output
Content-Type: application/vnd.ms-excel
MIME-Version: 1.0
Content-Disposition: attachment
EOF

else [ $5 == 'N' ]
echo "As per your request the Email won't trigger for the Concurrent request $4"
fi

exit 0;

Upvotes: 0

Views: 1361

Answers (2)

Egret
Egret

Reputation: 799

There's not an actual question here.

But if you're looking for feedback on the script, note that your use of passwords on the command line is insecure - they can be viewed on the process. You should pass them in via STDIN - this can be done via a pipe: https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:142212348066

or within the SQL*Plus session: Passing variable user,pass,sid in sqlplus comman

Upvotes: 0

ARGStackOvaFlo
ARGStackOvaFlo

Reputation: 305

The below code works well for sending Concurrent program output file in XLS format to desired email ID.

echo "starting the program"
echo "################################"
echo "Current Child Request ID :"$4
REQ_ID=$4
echo $REQ_ID
echo "Do you want the output through auto-generated email:" $5

if [ $5 == 'Y' ]
then
echo "As per your request generating email containing XLS output as attachment for the Concurrent request $4"
YEAH_FILE=
sqlplus -s <DB_Schema_Name>/<Schema_Password>@<Instance_Name> << EOF
set linesize 300;
set pagesize 0;
set echo off;
set serveroutput off;
set sqlblank off;
set feedback off;
set heading off;
set wrap off;
col "REQ" format 9999999;
select '$APPLCSF/out/*' || REQUEST_ID ||'*.xls ' from FND_CONC_REQ_SUMMARY_V where REQUEST_ID ='$REQ_ID'; spool off EOF

#Get the email ID
SEND_MAIL_ID = 'echo <Email_ID>'

cat <<'EOF' - $YEAH_FILE | /usr/sbin/sendmail $SEND_MAIL_ID
Subject: Email with XLS output
Content-Type: application/vnd.ms-excel
MIME-Version: 1.0
Content-Disposition: attachment
EOF

else [ $5 == 'N' ]
echo "As per your request the Email won't trigger for the Concurrent request $4"
fi

exit 0;

Upvotes: 0

Related Questions