oracle 基于PLSQL实现发送邮件 UTL_SMTL

 本博文介绍如何使用 UTL_SMTP来发送邮件

UTL_SMTP是基于SMTP协议来发送邮件

1、需要安装UTL_SMTP包

要利用oracle的系统包实现发送邮件的功能，必须先以sys用户登录执行以下两个脚本：

$ORACLE_HOME/rdbms/admin/utlsmtp.sql

$ORACLE_HOME/rdbms/admin/utltcp.sql

 

2、UTL_SMTP使用请看下面详细说明

DECLARE

  c UTL_SMTP.CONNECTION;

 

  PROCEDURE send_header(nameINVARCHAR2,headerINVARCHAR2)AS

  BEGIN

    UTL_SMTP.WRITE_DATA(c, name ||': ' || header || UTL_TCP.CRLF);

  END;

BEGIN

  /*连接邮件服务器*/

  c := UTL_SMTP.OPEN_CONNECTION('mail.daphne.com.cn');

  /*连接后执行与SMTP服务器的初始握手*/

  UTL_SMTP.HELO(c, 'mail.daphne.com.cn');

 

  /*该函数/过程执行的通用SMTP命令,执行登录认证*/

  UTL_SMTP.command(conn, 'AUTH LOGIN');

  UTL_SMTP.command(conn,

                   demo_base64.encode(utl_raw.cast_to_raw('testuser')));

  UTL_SMTP.command(conn,

                  demo_base64.encode(utl_raw.cast_to_raw('********')));

                  

  /*如果不用登陆认证，则直接执行下面语句即可*/

 

  /*发送邮箱*/

  UTL_SMTP.MAIL(c, 'testuser@mail.daphne.com.cn');

  /*接收邮箱*/

  UTL_SMTP.RCPT(c, 'zhenyun.asu@mail.daphne.com.cn');

 

  /*开始发送数据*/

  UTL_SMTP.OPEN_DATA(c);

 

  /*设置发送邮箱*/

  send_header('From','"Sender"<zhenyun.asu@mail.daphne.com.cn>');

  /*设置接收邮箱*/

  send_header('To','"Recipient"<fang.qing@mail.daphne.com.cn>');

  /*设置抄送邮箱*/

  send_header('Cc','<fang.qing@mail.daphne.com.cn>');

  /*设置邮件主题*/

  send_header('Subject','Hello');

 

  /*设置邮件内容*/

  UTL_SMTP.WRITE_DATA(c,UTL_TCP.CRLF || l_msg);

  UTL_SMTP.WRITE_RAW_DATA(c,

                          UTL_RAW.cast_to_raw(convert(UTL_TCP.CRLF ||

                                                     '测试邮件',

                                                     'ZHS16CGB231280')));

  /*发送邮件内容*/

  UTL_SMTP.CLOSE_DATA(c);

  /*断开当前会话及链接*/

  UTL_SMTP.QUIT(c);

EXCEPTION

  WHEN utl_smtp.transient_errorORutl_smtp.permanent_errorTHEN

    BEGIN

      UTL_SMTP.QUIT(c);

    EXCEPTION

      WHEN UTL_SMTP.TRANSIENT_ERRORORUTL_SMTP.PERMANENT_ERRORTHEN

        NULL;-- When the SMTP server is down orunavailable, we don't have

      -- a connection to the server. TheQUIT call will raise an

      -- exception that we can ignore.

    END;

    raise_application_error(-20000,

                            'Failed to send maildue to the following error: ' ||

                            sqlerrm);

END;

 

实例代码：D:\2_Techinques\6_Personal_summary\D2_Oracle_DB\SZ02_PLSQL技术文档

实例代码2.sql

 

3、将UTL_SMTL封装更加好用代码

  procedure create_mail(i_server_host varchar2,

                       i_sending_host varchar2,

                        o_con          out UTL_SMTP.CONNECTION)is

  begin

    o_con :=UTL_SMTP.OPEN_CONNECTION(i_server_host);

    UTL_SMTP.HELO(o_con,i_sending_host);

  end;

 

  procedure create_mail_auth(i_server_host varchar2,

                             i_sending_hostvarchar2,

                            i_username    varchar2,

                            i_passwd      varchar2,

                            o_con         outUTL_SMTP.CONNECTION)is

  begin

    o_con :=UTL_SMTP.OPEN_CONNECTION(i_server_host);

    UTL_SMTP.HELO(o_con,i_sending_host);

 

    UTL_SMTP.COMMAND(o_con, 'AUTH LOGIN');

    UTL_SMTP.COMMAND(o_con,

                    DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(i_username)));

    UTL_SMTP.COMMAND(o_con,

                    DEMO_BASE64.ENCODE(UTL_RAW.CAST_TO_RAW(i_passwd)));

  end;

 

  procedure send_mail(i_con   INOUTNOCOPY UTL_SMTP.CONNECTION,

                      i_sender varchar2)is

  begin

    UTL_SMTP.MAIL(i_con, i_sender);

  end;

  procedure recpt_mail(i_con  INOUTNOCOPY UTL_SMTP.CONNECTION,

                       i_recpt varchar2)is

  begin

    UTL_SMTP.RCPT(i_con, i_recpt);

  end;

 

  PROCEDURE write_header(i_con INOUTNOCOPY UTL_SMTP.CONNECTION,

                         name  INVARCHAR2,

                         header INVARCHAR2)AS

  BEGIN

    UTL_SMTP.WRITE_DATA(i_con, name ||': ' || header ||UTL_TCP.CRLF);

  END;

 

  PROCEDURE write_raw_header(i_con INOUTNOCOPY UTL_SMTP.CONNECTION,

                             name  INVARCHAR2,

                             header INVARCHAR2)AS

  BEGIN

    UTL_SMTP.WRITE_RAW_DATA(i_con,

                           UTL_RAW.cast_to_raw(convert(name ||': ' ||

                                                       header ||

                                                        UTL_TCP.CRLF,

                                                       'ZHS16CGB231280')));

  END;

 

  PROCEDURE write_raw_content(i_con  INOUTNOCOPY UTL_SMTP.CONNECTION,

                              contentINVARCHAR2)AS

  BEGIN

    UTL_SMTP.WRITE_RAW_DATA(i_con,

                           UTL_RAW.cast_to_raw(convert(UTL_TCP.CRLF ||

                                                       content,

                                                       'ZHS16CGB231280')));

  END;

 

  procedure header_from(i_con INOUTNOCOPY UTL_SMTP.CONNECTION,

                        i_mail varchar2)is

  begin

    write_header(i_con, 'From','"Sender"<' ||i_mail ||'>');

  end;

 

  procedure header_to(i_con INOUTNOCOPY UTL_SMTP.CONNECTION,

                      i_mail varchar2)is

  begin

    write_header(i_con, 'To','"Recipient"<' ||i_mail ||'>');

  end;

 

  procedure header_cc(i_con INOUTNOCOPY UTL_SMTP.CONNECTION,

                      i_mail varchar2)is

  begin

    write_header(i_con, 'Cc','"CC"<' ||i_mail ||'>');

  end;

 

  procedure header_subject(i_con    INOUTNOCOPY UTL_SMTP.CONNECTION,

                           i_subjectvarchar2)is

  begin

    write_raw_header(i_con, 'Subject', i_subject);

  end;

 

  procedure header_content(i_con    INOUTNOCOPY UTL_SMTP.CONNECTION,

                           i_contentvarchar2)is

  begin

    write_raw_content(i_con,i_content);

  end;

 

  procedure open_data(i_conINOUTNOCOPY UTL_SMTP.CONNECTION)is

  begin

    UTL_SMTP.OPEN_DATA(i_con);

  end;

  procedure close_data(i_conINOUTNOCOPYUTL_SMTP.CONNECTION)is

  begin

    UTL_SMTP.CLOSE_DATA(i_con);

  end;

 

  procedure free_mail(i_conINOUTNOCOPYUTL_SMTP.CONNECTION)is

  begin

    UTL_SMTP.QUIT(i_con);

  end;

 

  procedure send_mail_demois

    l_con UTL_SMTP.CONNECTION;

  begin

    /*初始化条件*/

 

    create_mail_auth('mail.daphne.com.cn',

                     'mail.daphne.com.cn',

                     'testuser',

                     '********',

                     l_con);

 

    send_mail(l_con, 'testuser@mail.daphne.com.cn');

 

    recpt_mail(l_con, 'zhenyun.asu@mail.daphne.com.cn');

 

    open_data(l_con);

 

  -- header_from(l_con, 'DPSCM.PT@mail.daphne.com.cn');

    header_to(l_con, 'zhenyun.asu@mail.daphne.com.cn');

    header_cc(l_con, 'zhenyun.asu@mail.daphne.com.cn');

    header_subject(l_con, 'zhenyun.asu hellmail');

    header_content(l_con, 'zhenyun.asu hellmail');

 

    close_data(l_con);

 

    free_mail(l_con);

 

  EXCEPTION

    WHEN utl_smtp.transient_errorORutl_smtp.permanent_errorTHEN

      BEGIN

        free_mail(l_con);

      EXCEPTION

        WHEN UTL_SMTP.TRANSIENT_ERRORORUTL_SMTP.PERMANENT_ERRORTHEN

          NULL;-- When the SMTP server is down orunavailable, we don't have

        -- a connection to the server. TheQUIT call will raise an

        -- exception that we can ignore.

      END;

      raise_application_error(-20000,

                              'Failed to send maildue to the following error: ' ||

                              sqlerrm);

  end send_mail_demo;