proc_update_mail.sql

REM Script Name		: tp_datamgt_bonds.sql
REM Author				: Craig Richards
REM Created			: 20 May 2013 
REM Last Modified	:
REM Version				: 1.0
REM
REM Modifications		:
REM
REM Description		: This procedure updates data

CREATE OR REPLACE PROCEDURE tp_datamgt_bonds
AUTHID CURRENT_USER
AS

--Variable Declaration

num_rows		NUMBER;
v_From			VARCHAR2(80) := 'From@mail.com';
v_Recipient	VARCHAR2(80) := 'To@mail.com';
v_Subject		VARCHAR2(80) := 'Your Update Statement';
v_Mail_Host	VARCHAR2(30) := 'mailhost';
v_Mail_Conn	UTL_SMTP.Connection;
CRLF        	VARCHAR2(2)  := CHR(13)||CHR(10);

BEGIN

-- Update SQL Statement

  UPDATE table
  SET active_flag = 'N'
  WHERE a=b;
  
  num_rows := SQL%ROWCOUNT;

-- Send an email with the amount of rows that have been updated

  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, v_Recipient);
  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: '     || v_Recipient || CRLF ||CRLF ||'Procedure Ran and there were ' || num_rows || ' rows updated '|| CRLF || CRLF || 'Regards' ||CRLF ||'DBA Team');

-- Exception Code

EXCEPTION
WHEN UTL_SMTP.Transient_Error OR UTL_SMTP.Permanent_Error THEN
  RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: '||SQLERRM);
  DBMS_OUTPUT.PUT_LINE (num_rows || ' Rows Updated');
END;
/