Issues while sending a mail having records fetched from SQL -all in UNIX


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Issues while sending a mail having records fetched from SQL -all in UNIX
# 1  
Old 07-11-2009
Issues while sending a mail having records fetched from SQL -all in UNIX

hi-I want to write a UNIX Script which will fetch records from Oracle database.I want to store these records in a file under fields like email address,name and the transaction_id and then fetch these records from the file and send the email to individual users with their transaction ID details.
# 2  
Old 07-11-2009
Quote:
Originally Posted by DeepSalwan
hi-I want to write a UNIX Script which will fetch records from Oracle database.I want to store these records in a file under fields like email address,name and the transaction_id and then fetch these records from the file and send the email to individual users with their transaction ID details.
More than likely you'll need to use a language like Python, Perl, or C to abstract that data.
# 3  
Old 07-11-2009
Code is like
Code:
#######################################################################
# Starting the JOB
#######################################################################
#LIST=`ls ${LOG_HM}/${JOB_NM}*.*`

echo "JOB:$JOB_NM started at $DATETIME_NOW" > ${LOGFILE} 2>&1
echo " " >> ${LOGFILE} 2>&1

######################################################################
# Setup Connection String for Connecting to Oracle #
######################################################################
if [[ ! -a $HOME/.pass_]]; then
echo "Password File $HOME/.pass doesn't exist" >> ${LOGFILE} 2>&1
echo "Returning Non-Zero Code" >> ${LOGFILE} 2>&1
exit 1;
fi
USER=`cat $HOME/.pass | cut -d'/' -f1`
PASSWD=`cat $HOME/.pass_cdm | cut -d'/' -f2`
CONNECT_STRING=$USER/$PASSWD


ble.
#***********************************************************************

*******************************************
function Search_And_Mail_To_DS
{
sqlplus -s <<//
$CONNECT_STRING
set verify off
set heading off

COLUMN param FOLD_AFTER

SELECT trnsctn_nbr ,FRST_NM ,eml_adrs
FROM systm_usr
WHERE
UPDT_DT like sysdate


SET HEAD ON FEEDBACK ON VERIFY ON
COLUMN param CLEAR
PROMPT sql_complete
exit;


while read -p LINE
do
# Start setting variables based on return values
if print $LINE | grep @yahoo.com
then
INPUT_RECORD_SIZE=$(print $LINE | awk -F: '{print $2}')
continue

# Process any Oracle errors

elif print $LINE | grep x
then
print "Oracle error occurred"
exit 1

# Start setting variables based on return values
elif print $LINE | grep @
then
INPUT_RECORD_SIZE=$(print $LINE | awk -F: '{print $2}')
continue

# Look for our loop termination marker and break out when found
elif print $LINE | grep sql_complete
then
break


fi
done
//

if [ ${?} -ne 0 ]; then
echo "Failed To Connect To Oracle" >> ${LOGFILE} 2>&1
echo "Returning Non-Zero Code" >> ${LOGFILE} 2>&1
exit 1;
fi

}


#***********************************************************************

*****************************************
#archive_log_files $LIST
echo "Global IDs for which NULL values are passed from WFSAD while 

update" >> ${LOGFILE} 2>&1
echo 

"-----------------------------------------------------------------------

--" >> ${LOGFILE} 2>&1
Search_And_Mail_To_DS>> ${LOGFILE} 2>&1
echo

This code doesnt work

Last edited by vgersh99; 07-11-2009 at 10:05 AM.. Reason: code tags, PLEASE!
# 4  
Old 07-11-2009
To keep the forums high quality for all users, please take the time to format your posts correctly.

First of all, use Code Tags when you post any code or data samples so others can easily read your code. You can easily do this by highlighting your code and then clicking on the # in the editing menu. (You can also type code tags [code] and [/code] by hand.)

Second, avoid adding color or different fonts and font size to your posts. Selective use of color to highlight a single word or phrase can be useful at times, but using color, in general, makes the forums harder to read, especially bright colors like red.

Third, be careful when you cut-and-paste, edit any odd characters and make sure all links are working property.

Thank You.

The UNIX and Linux Forums
# 5  
Old 07-11-2009
I can see the records being fetched as it can be viewed in the logs,but theeafter I need to mail them (-might be through a Do while loop) to individual user with their ids ...but dont know how...
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Issues sending emails using PostFix Mail Server

I'm unable to send email from my Linux server despite SMTP port 25 Active and Listening. # hostname TechX I checked the mail log ( /var/log/maillog ) and found the below error. I'm sharing all the ".cf" files seen in the error log. 1. # more /etc/postfix/main.cf # postfix... (0 Replies)
Discussion started by: mohtashims
0 Replies

2. UNIX for Dummies Questions & Answers

Sending e-mail from unix

Hello, I want to send an email from unix. I tried following commands: mailx -s "hello" manish.xxx@xxx.com < echo_manish and echo "Testing Mail" | mailx -s "hello" manish.xxx@xxx.com but in both the commands nothing is happening. I mean it is neither giving any error nor I am receiving... (7 Replies)
Discussion started by: manishdivs
7 Replies

3. UNIX for Dummies Questions & Answers

Issues in sending mail with attachements

Hi I am using the below command to send mail from unix with body as well as attachment. But the attachment is in encoded form in the body itself. I am not receiving as attachment: (cat body_success.txt;uuencode Log.txt Log.txt)| mailx -s "success" $ToID I am receiving... (5 Replies)
Discussion started by: pandeesh
5 Replies

4. UNIX for Dummies Questions & Answers

issues in sending mail

Hi, i am trying the below script for sending mail. if then count=`cat $sfile|wc -l` echo "There are $count abends.Please take care " > body.txt (cat body.txt;uuencode $sfile $sfile) | mailx -s "Alert:there are failures" pandeesh@gmail.com fi In the mail, i am getting like: ... (4 Replies)
Discussion started by: pandeesh
4 Replies

5. Shell Programming and Scripting

Executing SQL Query and sending a mail

Hi all, My reqirenet goes like this. Need to execute one select statement within the script and send a mail to the users with the number of records fecthed from the query. Please help.. Thanks. (3 Replies)
Discussion started by: Achiever
3 Replies

6. Shell Programming and Scripting

Importing data from PL/SQL then sending it through mail,HELP ME!

Is anyone here know how to make a script in UNIX which will do importing data from PL/SQL then sending it through mail? Can you give me sample script with explanation so it's easy to understand,Thank you very much,any suggestion or advice is welcome, (1 Reply)
Discussion started by: Atrap
1 Replies

7. Shell Programming and Scripting

Sending mail in unix

Dear Friends, I have a shell script where the mail is being sent like this: /usr/lib/sendmail -v ${CPA_ADMIN} CPA_ADMIN="xx@abc.com" Can we specify more than one email ids in this variable? Is ther eany limit to the number of email ids I can specify in this variable, to whom the mail... (3 Replies)
Discussion started by: Radhe
3 Replies

8. UNIX for Dummies Questions & Answers

sending mail from unix

Hi, I am using mailx command to send mail through Unix. But I am able to send mail only within my domain. If i want to send mail to some other server, it's not working. Like say If I want to send mail to someone on gmail or yahoo it's not working. but it's working fine within my company domain.... (3 Replies)
Discussion started by: anki_1
3 Replies
Login or Register to Ask a Question