Home » Infrastructure » Unix » Need to send email only if it has sql extract the data
Need to send email only if it has sql extract the data [message #338374] Mon, 04 August 2008 15:15 Go to next message
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 #338377 is a reply to message #338374] Mon, 04 August 2008 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/

Since you ignore Posting Guidelines, we ignore your post.

You're On Your Own (YOYO)!
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 Go to previous messageGo to next message
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,




Re: Need to send email only if it has sql extract the data [message #338380 is a reply to message #338374] Mon, 04 August 2008 15:48 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
This problem & solution have nothing to do with Oracle
& is simply a *nix script "problem" which can be solved by checking spool file length & acting appropriately.

This thread is locked now.
Previous Topic: 32 to 64 bit..
Next Topic: Need 8.1.7 client for AIX 3.5
Goto Forum:
  


Current Time: Thu Mar 28 18:36:30 CDT 2024