[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

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

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
IT_ETL
Asked:
IT_ETL
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
It is already in there:  whenever sqlerror exit

Is it not working?
0
 
Devinder Singh VirdiCommented:
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
 
Devinder Singh VirdiCommented:
So you are good, because your script already has that, as explained by slightwv.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
flow01Commented:
try
whenever sqlerror exit failure;

it should set a return code indicating failure
0
 
IT_ETLAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>. 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
 
Devinder Singh VirdiCommented:
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
 
IT_ETLAuthor Commented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now