Solved

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

Posted on 2011-09-22
8
632 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Accepted Solution

by:
flow01 earned 400 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 100 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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