Need to send email only if it has sql extract the data [message #338374] |
Mon, 04 August 2008 15:15 |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
I need to write the sql and send it out an email if it has any records then i need to send it out email with data saying that "it has issue" and if no data then i don't need to send it out any email.
Currently i have written some programs that retrieve the data and send it as an attachment using korn shell script but i am little confused as if there are no data then how can i stop it to send an email?
Because my unix script call the sql script and its sppol the file and send it out as an email even there are no data in the file attachment from running sql.
Thanks,
[Updated on: Mon, 04 August 2008 23:36] by Moderator Report message to a moderator
|
|
|
|
Re: Need to send email only if it has sql extract the data [message #338378 is a reply to message #338374] |
Mon, 04 August 2008 15:41 |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
Sorry i used code parser for without code, i appolizeed for this.
Sorry for the mistake.
I need to write the sql and send it out an email if it has any records then i need to send it out email with data saying that "it has issue" and if no data then i don't need to send it out any email.
Currently i have written some programs that retrieve the data and send it as an attachment using korn shell script but i am little confused as if there are no data then how can i stop it to send an email?
Because my unix script call the sql script and its sppol the file and send it out as an email even there are no data in the file attachment from running sql.
I am using following Unix Korn Shell Script part:
sqlplus user@${ORACLE_SID}/${user_pass} << EOD
@${ORACLE_BASE}/sql/${cur_dir}/Issue1.sql
@${ORACLE_BASE}/sql/${cur_dir}/Issue2.sql
EOD
#--------------------------------------------------------------------------
# Email Part
#--------------------------------------------------------------------------
from_mail="admin@adminDBA.com"
tmp_mail="poratips@ora.com"
if [[ -f /home/oracle/reports/Issue/Issue1.txt ]]
then
uuencode /home/oracle/reports/Issue/Issue1.txt Issue1.txt| mailx -s "MAJOR ISSUE" -r $from_mail $tmp_mail
else
echo "There is no file to send."
fi
if [[ -f /home/oracle/reports/Issue/Issue2.txt ]]
then
uuencode /home/oracle/reports/Issue/Issue2.txt Issue2.txt| mailx -s "MAJOR ISSUE" -r $from_mail $tmp_mail
else
echo "There is no file to send."
fi
And I follwoing is my sql part:
set linesize 400 pagesize 0 trimspool on feedback off verify off define off
spool /home/oracle/reports/Issue/Issue1.txt
SELECT ID, STATE, C_ID FROM Issue
WHERE STATE = 'NOT started'
AND C_ID IN ('230', '332')
AND I_DATE > TO_DATE (SYSDATE - 5/24);
And SQL2:
=========
set linesize 400 pagesize 0 trimspool on feedback off verify off define off
spool spool /home/oracle/reports/Issue/Issue2.txt
SELECT ID, STATUS, C_ID FROM Issue
WHERE STATUS = 'X'
AND C_ID IN ('230', '332')
AND I_DATE > TO_DATE (SYSDATE - 5/24);
Thanks,
|
|
|
|