Sending HTML email from ORACLE PLSQL


This little PLSQL example will send a HTML formatted email from Oracle PLSQL procedure. You would need this to generate a nice formatted emails with, for example, a listing of users or a tablespace report. Schedule this to run from DBA_JOBS or DBMS_SCHEDULER.


CREATE OR REPLACE PROCEDURE SEND_USER_LIST
AS
  v_message VARCHAR2(32000);
  conn UTL_SMTP.CONNECTION;
  v_sender_acct varchar2(100):='oracle@YOUR_ORGANIZATION.com'; -- change this to your company
  v_count number;
  v_hostname varchar2(100):='  ';
  v_displayhost varchar2(100);
BEGIN
  v_message:='<center><h3><i><font color=#000099>List of Oracle Users and their account statuses </font></i></h3></center><br>'||utl_tcp.CRLF;
  v_message:=v_message||'<table style="border: solid 0px #cccccc"  cellspacing="0" cellpadding="0"><tr BGCOLOR=#000099>';
  v_message:=v_message||'<td><b><font color=white>Username</font></td>';
  v_message:=v_message||'<td><b><font color=white>Account Status</font></td></tr>'||utl_tcp.CRLF;
  FOR I IN (select username, account_status from dba_users)
  LOOP
     v_message:=v_message||'<tr><td>'||i.username||'</td><td>'||i.account_status||'</td></tr>'||utl_tcp.CRLF;
  END LOOP;

  v_message:=v_message||'</table></body></html>'||utl_tcp.CRLF;
  conn:= utl_smtp.open_connection('ORACLE_HOSTNAME'); -- change this to the SMTP server, most likely, you can use the server where oracle is installed
  utl_smtp.helo(conn,'ORACLE_HOSTNAME'); -- change this to the SMTP server, most likely, you can use the server where oracle is installed
  utl_smtp.mail(conn,v_sender_acct);
  utl_smtp.rcpt(conn,'EMAIL_TO_SEND@YOUR_ORGANIZATION.com'); -- email you want to send the email to
  utl_smtp.open_data(conn);
  utl_smtp.write_data(conn,'content-type: text/html;');
  utl_smtp.write_data(conn,'MIME-Version: 1.0'||utl_tcp.CRLF);
  utl_smtp.write_data(conn,'To: '||'EMAIL_TO_SEND@YOUR_ORGANIZATION.com'||utl_tcp.CRLF); -- email you want to send the email to
  utl_smtp.write_data(conn,'Cc:'||utl_tcp.CRLF);
  utl_smtp.write_data(conn,'From: '||v_sender_acct||utl_tcp.CRLF);
  utl_smtp.write_data(conn,'Subject: List of Oracle Users and their account statuses'||utl_tcp.CRLF);
  utl_smtp.write_data(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(conn,v_message);
  utl_smtp.close_data(conn);
  utl_smtp.quit(conn);
END;
/

The sample output is below:

List of Oracle Users and their account statuses

Username Account Status
SYSTEM OPEN
SYS OPEN
ORACLE OPEN
DBSNMP OPEN
SYSMAN OPEN
MGMT_VIEW OPEN
XDB LOCKED
OPS$ORACLE OPEN
ANONYMOUS LOCKED
OUTLN OPEN
TSMSYS LOCKED
DIP LOCKED




Copyright © 2007, All rights reserved by RudnikConsulting Inc