|
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;
/
|