Adding couple of conditions to existing shell script

User_QDHXF

Hello,

I totally newbie towards Shell scripting. We have an existing script as below:

#!/bin/bash

# Script that will kick off reading from the Oracle view

# to get the list of providers into a json formatted file for use in the provider module. 

# The companion file is sql_query.sql to pull the correct fields from

# Oracle and then the data is massaged below to remove any unwanted characters

source ~/.bashrc

# Needed by SQL*Plus on DEV

# Old DEV - ORACLE_HOME=/app/app/oracle/product/12.2.0/client_1 ; export ORACLE_HOME

ORACLE_HOME=/app/oracle/app/oracle/product/19.0.0/client_1 ; export ORACLE_HOME

SQL_CMD=/app/oracle/app/oracle/product/19.0.0/client_1/bin/sqlplus

# Specify the location of the final file

#OUTFILE=/home/ec2-user/provider_list.json

#OUTDIR=/home/ec2-user

OUTDIR=/app/tableauTomcat/webapps/docs

OUTFILE=/app/tableauTomcat/webapps/docs/provider_list.json

PROVFILE=provider_list.json

# Development Credentials

CREDS=tabdata/[email protected]/dev

DATE=`date +%Y%m%d_%H_%M`

BASE_DIR=${OUTDIR}

if [ -f $BASE_DIR/$PROVFILE ]; then

  cp $BASE_DIR/$PROVFILE $BASE_DIR/$PROVFILE.$DATE

  /usr/bin/gzip $BASE_DIR/$PROVFILE.$DATE

fi

# Find and remove any files older than 3 days

find $BASE_DIR -name "${PROVFILE}.*" -a -mtime +3 -exec rm {} \;

echo ${OUTFILE}

echo ${ORACLE_HOME}

echo ${SQL_CMD}

echo ${PATH}

echo -n "Starting p_provider_timeline procedure: "

date '+%Y%m%d-%H%M%S'

${SQL_CMD} /nolog << EOF0

CONNECT ${CREDS}

exec p_provider_timeline;

I have been asked to do the following on this script:

1. Before anything gets executed from the script, check Oracle DB Connection.
2. If DB connection is successful, proceed normally.
3. If DB connection is unsuccessful, then send an email to admin team and quite from the script, without attempting to execute anything.

Can someone please help me how to achieve these? Any help is greatly appreciated.

Thanks.

User_IBB8Q

Hi User_QDHXF,

you can use the command mail at this environment? you can test using below command:

echo "this is a test" | mail -s "This is mail subject" <mail_address> 

if so, I expect something like below should give some result (replace <mail_address>).

<<

# Needed by SQL*Plus on DEV

ORACLE_HOME=/app/oracle/app/oracle/product/19.0.0/client_1 ; export ORACLE_HOME

SQL_CMD=/app/oracle/app/oracle/product/19.0.0/client_1/bin/sqlplus

# Specify the location of the final file

#OUTFILE=/home/ec2-user/provider_list.json

#OUTDIR=/home/ec2-user

OUTDIR=/app/tableauTomcat/webapps/docs

OUTFILE=/app/tableauTomcat/webapps/docs/provider_list.json

PROVFILE=provider_list.json

# Development Credentials

CREDS=............................................................

CONNECTION_MADE=$( ${SQL_CMD} /nolog << EOF

CONNECT ${CREDS}

set heading off feedback off verify off

select 'Connected' from dual;

exit

EOF

)

echo "Result:"

echo "<<<"

echo "${CONNECTION_MADE}" | grep -v SP2-

echo ">>>"

echo

if [ `echo "${CONNECTION_MADE}" | grep -c "Connected"` -eq 0 ] ; then

 echo "${CONNECTION_MADE}" | mail -s "No connection made to database" <mail_addres>

 exit 1

else

 echo "Connection made to database, so continue"

fi

>>

User_IBB8Q

Hi User_QDHXF,

you can use the command mail at this environment? you can test using below command:

echo "this is a test" | mail -s "This is mail subject" <mail_address> 

if so, I expect something like below should give some result (replace <mail_address>).

<<

# Needed by SQL*Plus on DEV

ORACLE_HOME=/app/oracle/app/oracle/product/19.0.0/client_1 ; export ORACLE_HOME

SQL_CMD=/app/oracle/app/oracle/product/19.0.0/client_1/bin/sqlplus

# Specify the location of the final file

#OUTFILE=/home/ec2-user/provider_list.json

#OUTDIR=/home/ec2-user

OUTDIR=/app/tableauTomcat/webapps/docs

OUTFILE=/app/tableauTomcat/webapps/docs/provider_list.json

PROVFILE=provider_list.json

# Development Credentials

CREDS=............................................................

CONNECTION_MADE=$( ${SQL_CMD} /nolog << EOF

CONNECT ${CREDS}

set heading off feedback off verify off

select 'Connected' from dual;

exit

EOF

)

echo "Result:"

echo "<<<"

echo "${CONNECTION_MADE}" | grep -v SP2-

echo ">>>"

echo

if [ `echo "${CONNECTION_MADE}" | grep -c "Connected"` -eq 0 ] ; then

 echo "${CONNECTION_MADE}" | mail -s "No connection made to database" <mail_addres>

 exit 1

else

 echo "Connection made to database, so continue"

fi

>>

User_QDHXF

@User_IBB8Q Thank you for the response. When I tried testing command line:

echo "this is a test" | mail -s "This is mail subject" <mail_address> 

I get the following message as below:

Send options without primary recipient specified.

Usage: mail -eiIUdEFntBDNHRVv~ -T FILE -u USER -h hops -r address -s SUBJECT -a FILE -q FILE -f FILE -A ACCOUNT -b USERS -c USERS -S OPTION users

I haven't received any mail yet. Please let me know if this would still resolve the issue?

User_IBB8Q

Hi @User_QDHXF ,

Please execute a grep on mail in your script directory, there might be another script which sends email.

otherwise contact your linux-administrator how you can send email.

You might create a small file and try:

mail -s "This is mail subject" -a <File_name> <email_address>

With kind regards.

User_QDHXF

Hi @User_IBB8Q ,

Actually your suggested code worked. I did get the mail when DB connection is failed. Much appreciate your help.

Thank you!!

user10174131

If you are just learning the shell, it will be helpful for you to install the "dash" shell from EPEL, and use it exclusively for writing scripts (keep bash for interactive use).

Dash is very close to a minimal POSIX shell, and Debian/Ubuntu use it as the system shell.

Dash will prevent you from using any "bashisms" or other features that reduce the portability of your shell scripts. Dash is also advertised to be 4x faster than bash.