This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,116 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Adding couple of conditions to existing shell script

User_QDHXF
User_QDHXF Member Posts: 27 Green Ribbon

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.

Best Answer

  • User_IBB8Q
    User_IBB8Q Member Posts: 2 Green Ribbon
    Answer ✓

    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

Answers

  • User_IBB8Q
    User_IBB8Q Member Posts: 2 Green Ribbon
    Answer ✓

    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_QDHXF
    User_QDHXF Member Posts: 27 Green Ribbon

    @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
    User_IBB8Q Member Posts: 2 Green Ribbon

    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
    User_QDHXF Member Posts: 27 Green Ribbon

    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
    user10174131 Member Posts: 49 Blue Ribbon
    edited Oct 11, 2022 2:52PM

    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.

    User_QDHXF