Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Logic in Shell script required

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Logic in Shell script required
# 1  
Old 11-17-2009
Logic in Shell script required
Hi,
I have the following in my shell script

Code:
sqlplus << EOF  username/password @$SCRIPTS_DIR/refresh.sql > /home/oracle/sql/refreshsql.log
EOF

RETCODE=$?
 
if [ $RETCODE -eq 0 ]; then
    echo "ERROR: sqlplus command finished with error code {RETCODE}"
    /usr/bin/mailx -s  "REFRESHING FMLA CONTACT MVIEW FAILED." test@company.com   /home/oracle/sql/refreshsql.log
exit 1
fi

Now, the result of this script is successful,meaning, the refresh.sql returns 'TRUE'. but I still get the email with subject ""REFRESHING FMLA CONTACT MVIEW FAILED."

My requirement is that I want the script to send email if the result of refresh.sql is false.

Please let me know the changes that needs to be done in above script.

Thanks
Last edited by Franklin52; 11-17-2009 at 03:05 PM.. Reason: Please use code tags!!
# 2  
Old 11-17-2009
The email will be send only for an successfull return code (which value is 0).
To be sur that your SQL command returns an error status in case of problem, add the following statements at the top of your sql script :
Code:
WHENEVER SQLERROR EXIT FAILURE ;
WHENEVER  OSERROR EXIT FAILURE ;

Code:
sqlplus << EOF username/password @$SCRIPTS_DIR/refresh.sql > /home/oracle/sql/refreshsql.log
EOF

RETCODE=$?

if [ $RETCODE -ne 0 ]; then
   echo "ERROR: sqlplus command finished with error code {RETCODE}"
   /usr/bin/mailx -s "REFRESHING FMLA CONTACT MVIEW FAILED." test@company.com /home/oracle/sql/refreshsql.log
   exit 1
fi

Jean-Pierre.
# 3  
Old 11-17-2009
It is unusual to see a SQL script return true or false, are you sure of that? I don't use Oracle these days but in DB2 for example a select will return 0 if successful and 1 if successful but no rows are returned. Generally speaking though 0 indicates success so sending the email if it equals 0 will not be what you want.

However you should anticipate all possible successful return codes from the script before deciding if not equal to zero is what you want to trigger the email.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script logic

Hi I have a requirement where I have to generate a mail/alert to the user if any QA check is failed. our oracle jobs run in unix environment and log file is created . Sample is given below . SELECT * FROM product.fact_tally WHERE period_id = '20130401' ; LIMIT 5; Ideally the above... (4 Replies)
Discussion started by: systemee
4 Replies

2. UNIX for Dummies Questions & Answers

Shell script required

Hi, I need shell script for getting the date in format from below text output IP IS 10.238.52.65 pun-ras-bng-mhs-01#show conf port 2/4 Building configuration... Current configuration: ! card ge3-4-port 2 ! port ethernet 2/4 no shutdown encapsulation dot1q (7 Replies)
Discussion started by: surender reddy
7 Replies

3. Shell Programming and Scripting

Help with shell script on Month Changing Logic

Hi Unix Experts, Happy Morning to all !! :) I am new to UNIX Shell Scripting and at my begineer level. To get acquainted to scripting, I am trying to create a script. The details/requirements of my script was to create a script with month changing logic in it so that on every 6th Working... (3 Replies)
Discussion started by: micky3112
3 Replies

4. Shell Programming and Scripting

help required with shell script

Hi, My input file as follws $ cat 1.txt ------- a aa aaa 11 b bb bbb 22 I am able to extract first and last column of a given line as follows. $ nawk '{print $1}' FS= RS= 1.txt | awk '{ $NF = ""; print }' a $ nawk '{print $1}' FS= RS= 1.txt | awk '{ print $NF}' 11 however, the... (4 Replies)
Discussion started by: bala123
4 Replies

5. Shell Programming and Scripting

Restart Logic for a Korn Shell Master Script

Hello All, I was wondering if someone can help me to develop restart logic for a Korn Shell program (a master script) that I am developing. The program is design to invoke a series of child processes (Korn Shell Scripts) whose function is to create a series of load files on a specified mount... (0 Replies)
Discussion started by: jonesdk5
0 Replies

6. Shell Programming and Scripting

shell script required...

There are two fields actually one is server name and the other one is Time. Based on time, there are 8 columns and these will be updated with the flag 1 if at all if there is any server name. Time Server name 15 to 18 18 to 21 21 to 24 00 to 03 03 to 06 06 to 09 09 to 12 Server... (3 Replies)
Discussion started by: venkatesht
3 Replies

7. Shell Programming and Scripting

URGENT SCRIPT LOGIC required

Hello friends, It will be great if we found some way to check this our: we have some databases (teradata and oracle). our applications(in java on weblogic) are using connection pools of these databases. we have a pair of userID and password for every pool. If we have any logic to test... (2 Replies)
Discussion started by: NIMISH AGARWAL
2 Replies

8. Shell Programming and Scripting

Shell script help required

Hi, Can someone help me with this small piece of code. DIRNAME=$(dirname $0) BASENAME=$(basename $0) DATA="${DIRNAME}/${BASENAME}.data" && . $DATA whats is meant by && . $DATA here... Regards, Abhishek (2 Replies)
Discussion started by: max29583
2 Replies

9. Shell Programming and Scripting

Help required on building the logic

Hi, Can anyone please help me on building the logic for writing a shell script which can delete blank lines from a file and count the number of duplicate lines in a file. Thanks, Indra (2 Replies)
Discussion started by: igandu
2 Replies

10. Shell Programming and Scripting

Query on a shell script logic

Hello people, I am new to shell scripting and hoping to get a quick answer to my query. I am writing a simple script which needs to call an executale which generates an output file. Once the output file is generated I have to do a couple of more task with that file. The executable ideally... (1 Reply)
Discussion started by: tipsy
1 Replies
Login or Register to Ask a Question