Shell Programming and Scripting

done | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

done | {
cat << !
begin
EMAIL_PROCEDURE;
end;
/
!
} | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC

CREATE OR REPLACE PROCEDURE EMAIL_PROCEDURE
IS
    v_From      VARCHAR2(80) := 'HKADM.Weekly';   
    v_Subject   VARCHAR2(80) := 'HKADM C3 Stats Fail - ' || to_char(sysdate,'DD') || ' ' || 
          rtrim(to_char(sysdate,'Month'), ' ') || ' ' || to_char(sysdate, 'YYYY');
    v_Mail_Host VARCHAR2(30) := 'smtp.XXX.com';
    v_Mail_Conn utl_smtp.Connection;
    crlf        VARCHAR2(2)  := chr(13)||chr(10);
    
    -- All live C3 databases to have not updated TODAY
    cursor c1 is select distinct hkadm.tbstat.tbs_svc_name
          from hkadm.tbstat, hkadm.srvc 
          where  hkadm.tbstat.tbs_svc_name = hkadm.srvc.svc_name 
          and hkadm.srvc.svc_app_name = 'c3' and hkadm.srvc.svc_app_type = 'L' 
          minus
          select distinct hkadm.tbstat.tbs_svc_name
          from hkadm.tbstat, hkadm.srvc 
          where hkadm.tbstat.tbs_date = TO_CHAR(SYSDATE, 'DD-MON-YY') and hkadm.tbstat.tbs_svc_name = hkadm.srvc.svc_name 
          and hkadm.srvc.svc_app_name = 'c3' and hkadm.srvc.svc_app_type = 'L'
          order by 1;
          
    -- Read emails from table
    cursor emails is select email from tbl_email;
    
    -- Place keeper for failed database collections
    db_fail VARCHAR2(4000);
        
    -- Name in introductory message generated from email
    recip VARCHAR2(20);
BEGIN
    -- Fill variable db_fail with the failed database names
    for val in c1 loop
       db_fail := db_fail || val.tbs_svc_name || crlf;
    end loop;
   
    -- Sends the email to all listed
    for email in emails loop
    -- Name in introductory message generated from email
    recip := UPPER(substr(SUBSTR(email.email, 1 ,INSTR(email.email, '.', 1, 1)-1),1,1)) || LOWER(substr(SUBSTR(email.email, 1 ,INSTR(email.email, '.', 1, 1)-1),2,19));
    v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
    utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
    utl_smtp.Mail(v_Mail_Conn, v_From);
    utl_smtp.Rcpt(v_Mail_Conn, email.email);   
    utl_smtp.Data(v_Mail_Conn,
       'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
       'From: '   || v_From || crlf ||
       'Subject: '|| v_Subject || crlf ||
       'To: '     || email.email || crlf ||
       crlf ||
       'Hi ' || recip || ',
       
The following databases failed to commit:'|| crlf ||	-- Message body
        db_fail ||
'
KR,
_______________________
Mike');
    
     utl_smtp.Quit(v_mail_conn);
     end loop;
EXCEPTION
     WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
        raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/

D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

cat << !
set heading off
set feedback off
set pagesize 0
set serveroutput on
select distinct tbstat.tbs_svc_name
          from tbstat, srvc
          where  tbstat.tbs_svc_name = srvc.svc_name
          and srvc.svc_app_name = 'c3' and srvc.svc_app_type = 'L';
!
} | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC | while read line; do
echo "$line collecting..."
./hkstats.ksh all $line
echo "$line completed..."
done | D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!

D:/user-applications/oracle/ora92/bin/sqlplus.exe -s $HKLOGIN/${HKPWD}@$HKSVC << !
begin
EMAIL_PROCEDURE;
end;
/
!