ORACLE UTL_SMTP: INSTALL AND EXAMPLE

---- To install UTL_SMTP ----

host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlsmtp
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mailhost ip address:25' scope=both;


---- Example UTL_SMTP ----



DECLARE
v_From VARCHAR2 := 'aaaaa@gmail.com';
v_Recipient VARCHAR2 := 'bbbb@gmail.com';
v_Recipient2 VARCHAR2 := 'ccccc@gmail.com';
v_Subject VARCHAR2 := 'this is a test of utl_smtp';
v_Mail_Host VARCHAR2 := 'mailhost name or mailhost IP address';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2 := chr(13)||chr(10);
BEGIN
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.Rcpt(v_Mail_Conn, v_Recipient2);
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 || ';' || v_Recipient2 || crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
/