Oracle 中利用Procedure 发邮件...

CREATE OR REPLACE PROCEDURE tnt_send_mail

( p_sender    IN VARCHAR2,

  p_receiver IN VARCHAR2,

  P_subject   IN VARCHAR2,

  p_message   IN VARCHAR2)

AS

  mailhost VARCHAR2(100) := '10.100.10.100';        --邮件服务器IP

  mail_conn utl_smtp.connection;

  PROCEDURE send_header(conn  IN OUT NOCOPY utl_smtp.connection,

    name  IN VARCHAR2,

    value IN VARCHAR2) IS

   BEGIN      

       --我服务器端的字符集是ZHS16GBK,使用GB2312才能支持中文



     utl_smtp.write_data(conn,name || ': =?GB2312?B?' ||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.

     cast_to_raw(value))) || '?='|| utl_tcp.crlf);  

      

   END;

BEGIN

  mail_conn :=utl_smtp.open_connection(mailhost);

  utl_smtp.helo(mail_conn,mailhost);

  utl_smtp.mail(mail_conn,p_sender);    -- sender

  utl_smtp.rcpt(mail_conn,p_receiver); -- receiver

  utl_smtp.open_data(mail_conn);

  send_header(mail_conn,'From', p_sender);

  send_header(mail_conn,'To', p_receiver);

  send_header(mail_conn,'Subject', p_subject);  --邮件主题

  

  UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13)|| chr(10));

  utl_smtp.write_data(mail_conn, 'MIME-Version: 1.0' || chr(13) || chr(10));

  utl_smtp.write_data(mail_conn, 'Content-type: text/plain;Charset=UTF8;' || chr(13) || chr(10));

  utl_smtp.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || chr(13) || chr(10));

  utl_smtp.write_data(mail_conn, chr(13) || chr(10));  

  utl_smtp.write_data(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_message))));

  utl_smtp.close_data(mail_conn);

  utl_smtp.quit(mail_conn);

EXCEPTION

  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN

    utl_smtp.quit(mail_conn);

    raise_application_error(-20000, sqlerrm);

  WHEN OTHERS THEN

    raise_application_error(-20001,

      'The following error has occured: ' || sqlerrm);   

End tnt_send_mail;