Send E-Mail through 'UTL_SMTP' package for DB Monitor

For send text Mail

declare
 l_mailhost    VARCHAR2(64) := '192.168.0.0';
 l_from        VARCHAR2(64) := 'oracle@test.com';
 l_to          VARCHAR2(64) := 'someone@gmail.com';
 l_subject     VARCHAR2(64) := 'Test';
 ts VARCHAR2(64);
 l_mail_conn   UTL_SMTP.connection;

BEGIN


 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
 UTL_SMTP.helo(l_mail_conn, l_mailhost);
 UTL_SMTP.mail(l_mail_conn, l_from);
 UTL_SMTP.rcpt(l_mail_conn, l_to);

 UTL_SMTP.open_data(l_mail_conn);

 UTL_SMTP.write_data(l_mail_conn, 'Date: '    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
 UTL_SMTP.write_data(l_mail_conn, 'From: '    || l_from || UTL_TCP.CRLF);
 UTL_SMTP.write_data(l_mail_conn, 'To: '      || l_to || UTL_TCP.CRLF);
 UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);
 --/*

 UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || '1'|| UTL_TCP.CRLF);

 UTL_SMTP.close_data(l_mail_conn);

 UTL_SMTP.quit(l_mail_conn);

END;
/


For Send Mail with HTML Table (Tablespace Usage)

DECLARE
 l_mailhost    VARCHAR2(64) := '192.168.0.0';
 l_from        VARCHAR2(64) := 'oracle@test.com';
 l_to          VARCHAR2(64) := 'someone@gmail.com';
 l_subject     VARCHAR2(64) := 'Tablespace Alert';
 I_message VARCHAR2(32000);
 l_mail_conn   UTL_SMTP.connection;



BEGIN

 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
 UTL_SMTP.helo(l_mail_conn, l_mailhost);
 UTL_SMTP.mail(l_mail_conn, l_from);
 UTL_SMTP.rcpt(l_mail_conn, l_to);

  I_message:='<center><h2><font color=#500909>Tablespace Usage Details</font></h2></center><br>'||utl_tcp.CRLF;
  I_message:=I_message||'<table style="border: solid 0px #cccccc"  cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
  I_message:=I_message||'<td><b><font color=white>Tablespace</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Size(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Used(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Free(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Used(%)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Free(%)</font></td></tr>'||utl_tcp.CRLF;


FOR I in 
 (
  SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
  ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
  100-ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) FREE_PCT_FULL
  FROM (
        SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
        0 TOTAL_MB, 0 MAX_MB
        FROM dba_free_space
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, 0 CURRENT_MB,
        SUM(bytes)/1024/1024 TOTAL_MB,
        SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
        FROM dba_data_files
        GROUP BY tablespace_name)
        GROUP BY tablespace_name
        having (ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100)) >80)

LOOP
    
    I_message:=I_message||'<tr><td>'||I.tablespace_name||'</td><td>'||I.CUR_SZ_MB||'</td><td>'||I.CUR_USE_MB||'</td><td>'||I.FREE_SPACE_MB||'</td><td>'||I.CUR_PCT_FULL||'</td><td>'||I.FREE_PCT_FULL||'</td></tr>'||utl_tcp.CRLF;
  
END LOOP;


  I_message:=I_message||'</table></body></html>'||utl_tcp.CRLF;

  
  
  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn,'content-type: text/html;');
  UTL_SMTP.write_data(l_mail_conn,'MIME-Version: 1.0'||utl_tcp.CRLF);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: '    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'From: '    || l_from || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'To: '      || l_to || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);
  
  UTL_SMTP.write_data(l_mail_conn,'<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; ">'||
  utl_tcp.CRLF||'<content="MSHTML 6.00.2800.1276" name=GENERATOR>'||utl_tcp.CRLF||'<HTML><BODY>');
  UTL_SMTP.write_data(l_mail_conn,I_message);
  
  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);

END;

/


For Send mail with multiple HTML Tables (Tablespace Usage and Invalid Objects)

DECLARE
 l_mailhost    VARCHAR2(64) := '192.168.0.0';
 l_from        VARCHAR2(64) := 'oracle@test.com';
 l_to          VARCHAR2(64) := 'someone@gmail.com';
 l_subject     VARCHAR2(64) := 'Daily Health Monitor - KIA';
 I_message VARCHAR2(32000);
 l_mail_conn   UTL_SMTP.connection;
 I_invalid_obj number(10);


BEGIN

 l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
 UTL_SMTP.helo(l_mail_conn, l_mailhost);
 UTL_SMTP.mail(l_mail_conn, l_from);
 UTL_SMTP.rcpt(l_mail_conn, l_to);

  I_message:='<center><h2><font color=#500909>Tablespace Usage Details</font></h2></center><br>'||utl_tcp.CRLF;
  I_message:=I_message||'<table style="border: solid 0px #cccccc"  cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
  I_message:=I_message||'<td><b><font color=white>Tablespace</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Size(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Used(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Free(MB)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Used(%)</font></td>';
  I_message:=I_message||'<td><b><font color=white>Total Free(%)</font></td></tr>'||utl_tcp.CRLF;


FOR I in 
 (
 select * from
 (
 SELECT tablespace_name, ROUND(SUM(total_mb)) CUR_SZ_MB, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, 
  ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, 
  100-ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) FREE_PCT_FULL
  FROM (
        SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
        0 TOTAL_MB, 0 MAX_MB
        FROM dba_free_space
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, 0 CURRENT_MB,
        SUM(bytes)/1024/1024 TOTAL_MB,
        SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
        FROM dba_data_files
        GROUP BY tablespace_name)
        GROUP BY tablespace_name
        
    union
    
  SELECT tablespace_name,round((tablespace_size/1024)/1024) CUR_SZ_MB,
  round(((tablespace_size-free_space)/1024)/1024) CUR_USE_MB, round((free_space/1024)/1024) FREE_SPACE_MB,
  round(((((tablespace_size-free_space)/1024)/1024)/((tablespace_size/1024)/1024))*100) CUR_PCT_FULL,
  round((((free_space/1024)/1024)/((tablespace_size/1024)/1024))*100) FREE_PCT_FULL
  FROM   dba_temp_free_space
  
  )
  where CUR_PCT_FULL >80
  order by CUR_PCT_FULL desc
  )

LOOP
    
    I_message:=I_message||'<tr><td>'||I.tablespace_name||'</td><td>'||I.CUR_SZ_MB||'</td><td>'||I.CUR_USE_MB||'</td><td>'||I.FREE_SPACE_MB||'</td><td>'||I.CUR_PCT_FULL||'</td><td>'||I.FREE_PCT_FULL||'</td></tr>'||utl_tcp.CRLF;
  
END LOOP;
   select count(*) into I_invalid_obj from dba_objects where status='INVALID';
   
  I_message:=I_message||'</table><br/><br/><br/>'||utl_tcp.CRLF;
  I_message:=I_message||'<center><h2><font color=#500909>Invalid Objects</font></h2></center>'||utl_tcp.CRLF;
  I_message:=I_message||'<h3><font color=#D00505>Total Number of Invalid Objects : '||I_invalid_obj||'</font></h3>'||utl_tcp.CRLF;
  I_message:=I_message||'<table style="border: solid 0px #cccccc"  cellspacing="1" cellpadding="1" width="100%"><tr BGCOLOR=#000099>';
  I_message:=I_message||'<td><b><font color=white>Owner</font></td>';
  I_message:=I_message||'<td><b><font color=white>Object Name</font></td>';
  I_message:=I_message||'<td><b><font color=white>Object Type</font></td>';
  I_message:=I_message||'<td><b><font color=white>Status</font></td></tr>'||utl_tcp.CRLF;
  
  
  FOR II in
  (
  select owner,object_name,object_type,status from dba_objects where status='INVALID')
  
  LOOP
    
    I_message:=I_message||'<tr><td>'||II.owner||'</td><td>'||II.object_name||'</td><td>'||II.object_type||'</td><td>'||II.status||'</td></tr>'||utl_tcp.CRLF;
  
END LOOP;

  I_message:=I_message||'</table></body></html>'||utl_tcp.CRLF;

  
  
  UTL_SMTP.open_data(l_mail_conn);
  
  UTL_SMTP.write_data(l_mail_conn,'content-type: text/html;');
  UTL_SMTP.write_data(l_mail_conn,'MIME-Version: 1.0'||utl_tcp.CRLF);
  
  UTL_SMTP.write_data(l_mail_conn, 'Date: '    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'From: '    || l_from || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'To: '      || l_to || UTL_TCP.CRLF);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);
  
  UTL_SMTP.write_data(l_mail_conn,'<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; ">'||
  utl_tcp.CRLF||'<content="MSHTML 6.00.2800.1276" name=GENERATOR>'||utl_tcp.CRLF||'<HTML><BODY>');
  UTL_SMTP.write_data(l_mail_conn,I_message);
  
  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);

END;

/