Interacting with SQL and PL/SQL from shell scripts

Today’s post is a  shell script which I had written for monitoring and storing the status of network links in Oracle tables. I am posting it here since it serves as a pretty good example of interacting with SQL and PL/SQL from a shell script including the passing of script variables.

#!/bin/bash
# This script pings the IP addresses provided to determine the status of the network link.
# In addition to logging the status in a log file, it also updates a table in the Oracle database
# with the status of the link. Additionally, if the status of a link has changed since it was
# last checked, records are inserted into the SMS tables(so that the SMS sending program can
# send the link status to the appropriate persons).

#------------------------------------------------------------------------------------------------#
# Enter the list of the IP addresses which you want to ping. Seperate multiple entries with spaces
#HOSTS="180.100.4.9 180.100.4.45 180.100.4.69"
HOSTS="192.168.56.10"
#------------------------------------------------------------------------------------------------#

#-----------------------------------------------------------------------------------------------#
# Enter the 10-digit phone numbers where texts will be sent. Seperate multiple entries with spaces.
PHONES="9999999999 8888888888"
#-----------------------------------------------------------------------------------------------#

# Count the number of phone numbers provided
NUMBER_COUNT=0
for myPhone in $PHONES
do
NUMBER_COUNT=$((NUMBER_COUNT+1))
done

#-------------------------------------------------------------------------#
# Number of packets to be sent to each IP address. Change this as required
COUNT=3
#-------------------------------------------------------------------------#

# This part will check the response of the IP pings.
# All packets received -- Link is UP
# No packets received -- Link is DOWN
# Some packets are received -- Link is experiencing packet loss
for $HOST in $HOSTS
do
 STATUS=UP
 count=$(ping -c $COUNT $HOST | grep 'received' | awk -F',' '{ print $2 }' | awk '{ print $1 }')
 if [ $count -eq 0 ]; then
 # 100% failed
 STATUS=DOWN
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 elif [ $count -gt 0 ] && [ $count -lt $COUNT ]; then
 # Some packets were received
 STATUS=PACKET_LOSS
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 else
 # 100% received
 echo "$HOST: $STATUS on $(date)" >> link_status.log
 fi

#Insert the link status into Oracle tables
sqlplus -s apps/apps <> link_status.log
set verify off
set heading off
set feedback off
set serveroutput on;
declare
v_ip varchar2(20);
v_old_status varchar2(15);
v_sms_id number;
v_sms_text varchar2(60);
v_server_name varchar2(20);
v_all_phones varchar2(100);
v_phone_no number;
v_docket_id varchar2(10);
begin
v_all_phones:='$PHONES';

--Fetch the previous status of the link
select status, server_name into v_old_status, v_server_name from xxx_link_status where server_ip='$HOST';

--Update table with current status of the link
update xxx_link_status set status='$STATUS',last_update=sysdate where server_ip='$HOST';

--If the current status is different from the previous status, then insert into SMS tables so that
--the SMSs will be sent during the next run of the SMS program
if v_old_status'$STATUS' then
 v_sms_text:=v_server_name||' link status: '||'$STATUS';

--This is required for the SMS table
 select sms_seq.nextval
 into v_sms_id
 from dual;

--This is required for the SMS table
 select docket_seq.nextval
 into v_docket_id
 from dual;

--Insert a record into the SMS_MESSAGES table
 BEGIN
 insert into sms_messages
 (sms_id, sms_date, sms_text, docket, resource_id)
 values
 (v_sms_id, sysdate, v_sms_text, v_docket_id, '');
 commit;
 dbms_output.put_line('Successfully updated sms_messages');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update sms_messages');
 END;

--Insert one record in SMS_MESSAGES for each phone number to which the SMS is to be sent
 for i in 1..$NUMBER_COUNT loop
 select to_number(substr(v_all_phones,((11*i)-10),10)) into v_phone_no from dual;

select docket_seq.nextval
 into v_docket_id
 from dual;
 BEGIN
 execute immediate
 'insert into sms_numbers values ('
 ||v_sms_id||
 ','||v_phone_no||
 ','||'''N'''||
 ','||'NULL'||
 ','||v_docket_id||
 ','||'NULL'||
 ')';
 commit;
 dbms_output.put_line('Successfully updated sms_numbers');
 EXCEPTION
 when others then
 dbms_output.put_line('Could not update SMS_NUMBERS');
 END;

end loop;
end if;
end;
/
exit;
EOM

done
echo '++++++++================================================++++++++' >> link_status.log