how to load the output of the Concurrent Request into a BLOB type
written by rahul pardeshi1 , October 24, 2007

Hi
how to load the output of the Concurrent Request into a BLOB type
is it stored somewhere in the database
Thanks,
Rahul.

Comments
written by Nitin Jain , October 25, 2007

Hi Santosh/Anil,

My suggestion here would be to use a shell script to achieve the same functionality. We have been doing this in plenty of our projects. UNIX shell scripts are more powerful than PL/SQL and require less coding as compared to PL/SQL.

We have had issues in using PL/SQL for emailing attachments (like excel documents containing macros in them and size > 100 KB). The fix to that lies in a 10g database upgrade.

Hence suggest readers/techies to make use of shell scripts than PL/SQL.

Hope this piece of info helps.

Thanks,
Nitin

Sample Code
written by Nitin Jain , October 25, 2007

Hi all,

I am pasting a sample shell script that can be useful for all readers.

#====================================================== =====
echo "XXFND_EMAIL_RPT.prog started "

if [ $# = 0 ]
then
echo "No parms were passed to the Shell Program "
exit 1
fi

V_COUNTER=0
while [ "$V_COUNTER" -le $# ]
do
#echo "Passed Parm $V_COUNTER : c"
case "$V_COUNTER" in
0) echo "$0" ;;
1) echo "$1"
V_LOGIN="$1";;
2) echo "$2" ;;
3) echo "$3" ;;
4) echo "$4"
V_CURRENT_REQ_ID="$4";;
5) V_EMAILID="$5";;
6) V_CONC_PGM_EMAILED="$6";;

esac
V_COUNTER=`expr $V_COUNTER 1`
done

echo "Current Req ID=$V_CURRENT_REQ_ID"
echo $V_EMAILID
echo $V_CONC_PGM_EMAILED

#====================================================== =====
# derive other settings
#====================================================== =====

FILE_IN_BASE=o${V_CURRENT_REQ_ID}.out # output file generated by Application
FILE_ERR_BASE=l${V_CURRENT_REQ_ID}.log # log file generated by Application
FILE_OUT=${APPLCSF}/${APPLOUT} # output file directory
FILE_LOG=${APPLCSF}/${APPLLOG} # log file directory
FILE_IN=${FILE_OUT}/${FILE_IN_BASE}
FILE_ERR=${FILE_LOG}/${FILE_ERR_BASE}

#====================================================== =====
#Email Body
#====================================================== =====
MESSAGE="Hi,

Please find attached the output of the XXX Invoice Validation Report.

Thanks,
ENP-FIN"


#====================================================== =====
#Check for Outfile Validity and existence
#====================================================== =====

DATAFILE_COUNT=`ls -l $FILE_IN | wc -l`

if [ $DATAFILE_COUNT -eq 1 ]
then
echo "One file!"
fi

if [ $DATAFILE_COUNT -lt 1 ]
then
echo "No input file exists"
exit 2
fi

echo ======================================================= ========
echo "Emailing Outfile to $5"
echo ======================================================= ========

(echo "$MESSAGE"; uuencode $FILE_IN Report.txt)| mailx -s "XXX Invoice Validation Report for Request ID:$V_CURRENT_REQ_ID" $V_EMAILID

exit 0
#====================================================== =====

Hope this helps.

Cheers,
Nitin

wonderfull
written by sudarsan , October 25, 2007

wonderfull.......nice code

Thx all :)-
written by Nitin Jain , October 26, 2007

Thx all, i'll see if i can contribute more to this group

...
written by rahul pardeshi1 , October 28, 2007

Hi,

Thanks, for your help
i just wanted to know how to call the shell script
as a concurrent program in application
Thanks,
Rahul

Thanks,
Rahul.

...
written by test , November 15, 2007

how do i load outfile into blob table

How to pass parameters to shell script
written by agadudasu , December 29, 2007

Hi,

I need to pass Email id dynamically to a Shell Script which is an exe file of conc.program.

Thanks,
AJAY

...
written by SrinivasReddy Chilkuri , January 11, 2008

Thx Anil/Santosh,
when executing the procedure (spsendemail)which is used to send the output of concurrent programm,i am getting the error
ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 43
ORA-06512: at "SYS.UTL_SMTP", line 279
ORA-06512: at "APPS.PKGSENDMAIL", line 136
ORA-06512: at "APPS.SPSENDEMAIL", line 136
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 2

execution statement is:
begin
spsendemail('xxsan','SANTELOO','en','US','TEXT','sriniv asreddi.ch@gmail.com')
end;

**and onemorething i have used this is for not XML reports(reports 6i,concurrent programm).

please help me

Thx
SrinivasReddy

Shell script for email
written by prakaash , January 22, 2008

Thanks Anil. This discussion really helping me.
My question is in shell script written by Nitin .. I have a requirement to send PDF docs(which are out put of one concurrent request) to users through e-mail. Iam using almost same code as listed above and it is working perfectly if the mail recipient is using outlook otherwise if the email id is normal yahoo,gmail the attachments are decoded. Is there any way to make this script to work for even normal yahoo,gmail email-ids.
Thanks
Prakaash

Output of Report 6i via email.
written by Harshita , January 23, 2008

Hi Nitin/Anil,
I need to mail the output of a Concurrent Program. Can you tell me how I can use the shell script given by Nitin ?
If i did so, what are the values of V_CURRENT_REQ_ID, $1, $2 etc ?
Should I first call the Report in the shell script?
Please help.
Thanks,
Harshita

Shell Script to send email with file attachment
written by Gowri , January 30, 2008

Hi Nitin/Anil,

I am using shell script to send .txt file has mail to users.

This '.txt' file has already been generated/created through a Concurrent Program of PL/SQL using UTL File Write Package.Because of which the file has been genearated in ASCII mode.

If i use unnencode command as below:
uuencode $LV_HOLD_LOG_FILENAME | mailx -s "$LV_PROGRAM_NAME" $LV_EMAIL_LIST

to send the file attachment, at the receivers end the file is not in correct format.
Because uuencode, transfers file in Binary mode, but the file is already in ASCII mode.

How to overcome this issue? Thanks in advance.

Regards,
Gowri

In response to Harshita
written by SureshV , February 06, 2008

There are 2 ways on how you can achieve this

1) Write the shell script code to send mail and save it in the bin directory. Create a concurrent program for this shell script.
2) In the after report trigger of your report call the shell script program using fnd_request.submit_request

The other way could be to directly call report from the shell script and after the report is completed use mailx utility to send output file.

need a shell script which saves the report output into a specific location immediately after the execution of the report.
written by dhanunjaya , March 20, 2008

Hi all.
please guide me..i need a shell script which has to save/move the output of a report in to specific location at the end of the concurrent request.

waiting..