Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

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 << EOM >> 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

Advertisements

4 responses to “Interacting with SQL and PL/SQL from shell scripts

  1. vogash December 25, 2013 at 7:32 pm

    Hi

    I tried to use some samples from your script, but i am not able print to the log something like dbms_output.put_line(‘Successfully updated sms_messages’);

    since i execute sqlplus with -S parameter. By removing it I got Oracle garbage like version, connected,etc.

    • oracleappsnotes March 24, 2014 at 4:13 pm

      Hi Vogesh,
      Did do a ‘set serveroutput on’ prior to calling dbms_output.put_line?

      • oracleappsnotes April 9, 2014 at 2:19 pm

        Please check if you have write permissions of the directory/file that you are writing your log to. Also check if you are redirecting your output properly to the log file after specifying the here document identifier, EOM. It works for me as noted below. You can try running the script test.sh below(after changing the apps password).

        [appusr@mysrv ~]$ cat test.sh
        #!/bin/bash

        sqlplus -s apps/apps <> link_status.log
        set verify off
        set heading off
        set feedback off
        set serveroutput on;
        begin

        dbms_output.put_line(‘Successfully updated sms_messages’);

        end;
        /
        exit;
        EOM
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$ sh test.sh
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$ ls
        link_status.log test.sh
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$
        [appusr@mysrv ~]$ cat link_status.log
        Successfully updated sms_messages
        [appusr@mysrv ~]$

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: