[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to run sql block using shell script and exit if error occurs?

Posted on 2011-09-22
8
Medium Priority
?
641 Views
Last Modified: 2012-05-12
I am running a simple sql block using below shell script. There is a syntax error in sql block so whenever below shell script try to run sql block using sqlplus command, this script should fail and send a "failure_subject" email. But every time I run this test script I get "success_subject" email. Please advise what I am missing here.


############################################################################
# Oracle Environment Variables
############################################################################
. $HOME/.profile

ORA_USER=user
ORA_PWD=password
############################################################################
# Local Variables
############################################################################
run_date=`date '+%Y%m%d'`
run_time=`date '+%H%M%S'`

shell_dir=$HOME/analytics/bin
sql_dir=$HOME/analytics/sql
tmp_dir=$HOME/analytics/tmp
log_dir=$HOME/analytics/log

progName=${0##*/}
tmp_file1=${tmp_dir}/${progName%%.*}_${run_date}${run_time}.1.sql
log_file=${log_dir}/${progName%%.*}_${run_date}${run_time}.log

success_subject="Test script job success: $run_date"
failure_subject="Test script job failure: $run_date"
success_mailto="xcm0@txu.com"
failure_mailto="xcm0@txu.com"

start_time=`date +%Y-%m-%d-%H:%M:%S`

cat <<- EOF > $tmp_file1
set echo off;
set heading off;
set feedback off;
whenever sqlerror exit;
SELECT SYSDATE,         --Syntax error so script should fail and exit
from dual;
exit;
EOF

seg_snap_date=`sqlplus -s ${ORA_USER}/${ORA_PWD}@ADWPRD @$tmp_file1` >> ${log_file}

#Script should fail to execute above statement

if [[ $? -ne 0 ]]; then
    mailx -s"$failure_subject" "$failure_mailto" < $log_file > /dev/null
    cat $log_file
    exit 1
fi
#I should get an email from above if statement block

if [[ $? -eq 0 ]]; then
    mailx -s"$success_subject" "$success_mailto" < ${log_file}
fi

#But I am getting an email from above block

cat $log_file

exit $?
 
0
Comment
Question by:IT_ETL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36582227
It is already in there:  whenever sqlerror exit

Is it not working?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36582268
Simple example:

run the following
select count(*) from asdfiasfj;   --- note this table is not present
you will get Ora 942 error table or view does not exists

---- Now run
whenever sqlerror exit;
select count(*) from asdfiasfj;  
--- your session will be disconnected.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36582274
So you are good, because your script already has that, as explained by slightwv.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 20

Accepted Solution

by:
flow01 earned 1600 total points
ID: 36582278
try
whenever sqlerror exit failure;

it should set a return code indicating failure
0
 

Author Comment

by:IT_ETL
ID: 36582669
I have modified the script little bit. I have deleted "whenever sqlerror exit" line and just ran the sql block using sqlplus command. The script ran and I got an email with "success_subject" but I really should have received an email that has "failure_subject". Please see below code and advise.

############################################################################
# Oracle Environment Variables
############################################################################
. $HOME/.profile

ORA_USER=user
ORA_PWD=password
############################################################################
# Local Variables
############################################################################
run_date=`date '+%Y%m%d'`
run_time=`date '+%H%M%S'`

shell_dir=$HOME/analytics/bin
sql_dir=$HOME/analytics/sql
tmp_dir=$HOME/analytics/tmp
log_dir=$HOME/analytics/log

progName=${0##*/}
tmp_file1=${tmp_dir}/${progName%%.*}_${run_date}${run_time}.1.sql
log_file=${log_dir}/${progName%%.*}_${run_date}${run_time}.log

success_subject="Test script job success: $run_date"
failure_subject="Test script job failure: $run_date"
success_mailto="xcm0@txu.com"
failure_mailto="xcm0@txu.com"

start_time=`date +%Y-%m-%d-%H:%M:%S`
echo "$start_time" >> ${log_file}

cat <<- EOF > $tmp_file1
set echo off;
set heading off;
set feedback off;
SELECT SYSDATE,
from dual;
exit;
EOF

sqlplus -s ${ORA_USER}/${ORA_PWD}@ADWPRD @$tmp_file1

#Script should fail to execute above statement

if [[ $? -ne 0 ]]; then
    mailx -s"$failure_subject" "$failure_mailto" < $log_file > /dev/null
    cat $log_file
    exit 1
fi
#I should get an email from above if statement block

if [[ $? -eq 0 ]]; then
    mailx -s"$success_subject" "$success_mailto" < ${log_file}
fi

#But I am getting an email from above block

cat $log_file

exit $?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 36582715
>>. I have deleted "whenever sqlerror exit" line

Why?  This is the line that should cause the error code.  Did you add 'failure' like suggested above?

Also, my shell scripting is rusty but I think you have a logic issue:  if the ret. code sends the fail message, your next if check for. $? For success will likely be based off the mailx command.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36582812
You can add the following as well to check for any Ora- errors:-

cat <<- EOF > $tmp_file1
set echo off;
set heading off;
set feedback off;
SPOOL /home/work/log/File1.log  ----------------- New line
SELECT SYSDATE, from dual;
exit;
EOF

CNT=`grep -i ora- /home/work/log/File1.log |wc -l`   ------------ New Line

if [[ $CNT -ne 0 ]]; then  --------------------- New Line
    mailx -s"$failure_subject" "$failure_mailto" < $log_file > /dev/null
    cat $log_file
    exit 1
fi

>>but I really should have received an email that has "failure_subject
Since you have put comma after sysdate, log file will reflect Ora- error and you will get desire result.

0
 

Author Comment

by:IT_ETL
ID: 36583264
I have added below line as suggested in the above script and it is working now.

............................................
............................................
whenever sqlerror exit failure;
............................................
............................................

Thanks everyone for your suggesstion.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Utilizing an array to gracefully append to a list of EmailAddresses
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question