Solved

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

Posted on 2011-09-22
8
624 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

815 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now