?
Solved

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

Posted on 2011-09-22
8
Medium Priority
?
657 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 78

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 21

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 78

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

592 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