PL/SQL 下邮件发送程序

      对DBA而言,尽管在os级别下发送邮件是轻而易举的事情,然而很多时候我们也需要在PL/SQL中来发送邮件,比如监控job的执行状况等。本文根据网友(源作者未考证)的代码将其改装并封装到了package,感谢这位网友的无私奉献。文章首先给出演示调用该包发送邮件的情形后面给出了完整的代码。经测试Oracle 10g,Oracle 11g下均可用。关于os下发送邮件可参考:不可或缺的 sendEmail

 

1、调用SENDMAIL_PKG来发送邮件

gx_admin@SYBO2SZ> set serveroutput on;
gx_admin@SYBO2SZ> DECLARE 
  2    P_RECEIVER VARCHAR2(32767);
  3    P_SUB VARCHAR2(32767);
  4    P_TXT VARCHAR2(32767);
  5    ERR_NUM NUMBER;
  6    ERR_MSG VARCHAR2(32767);
  7  
  8  BEGIN 
  9    P_RECEIVER := ‘robinson.chen@12306.com‘;
 10    P_SUB := ‘Test mail‘;
 11    P_TXT := ‘This is a test mail.‘;
 12    ERR_NUM := NULL;
 13    ERR_MSG := NULL;
 14  
 15    SENDMAIL_PKG.SENDMAIL ( P_RECEIVER, P_SUB, P_TXT, ERR_NUM, ERR_MSG );
 16  
 17    DBMS_OUTPUT.Put_Line(‘ERR_NUM = ‘ || TO_CHAR(ERR_NUM));
 18    DBMS_OUTPUT.Put_Line(‘ERR_MSG = ‘ || ERR_MSG);
 19  
 20    DBMS_OUTPUT.Put_Line(‘‘);
 21  
 22    COMMIT; 
 23  END;
 24  /
ERR_NUM = 0
ERR_MSG =

PL/SQL procedure successfully completed.


2、邮件发送结果

     PL/SQL 下邮件发送程序

3、原代码

--specification section
CREATE OR REPLACE PACKAGE "SENDMAIL_PKG"
IS
   PROCEDURE sendmail (p_receiver       VARCHAR2,
                       p_sub            VARCHAR2,
                       p_txt            VARCHAR2,
                       err_num      OUT NUMBER,
                       err_msg      OUT VARCHAR2);
END;
/

--body section
CREATE OR REPLACE PACKAGE BODY "SENDMAIL_PKG"
IS
   PROCEDURE sendmail (p_receiver       VARCHAR2,
                       p_sub            VARCHAR2,
                       p_txt            VARCHAR2,
                       err_num      OUT NUMBER,
                       err_msg      OUT VARCHAR2)
   IS
      /*   p_receiver   =>  receiver
             p_sub              =>  mail subject
             p_txt                => mail content
      */
      p_user                         VARCHAR2 (30) := NULL;
      p_pass                         VARCHAR2 (30) := NULL;
      p_sendor                       VARCHAR2 (40) := ‘DBA@gotrade.com‘;
      p_server                       VARCHAR2 (20)
                           --             := system_pkg.get_sys_para_value (‘TC_SMTP_IP‘); --‘192.168.7.65‘;
                                        :=‘192.168.7.65‘;
      p_port                         NUMBER := 25;
      p_need_smtp                    NUMBER := 0;
      p_subject                      VARCHAR2 (4000);
      l_crlf                         VARCHAR2 (2) := UTL_TCP.crlf;
      l_sendoraddress                VARCHAR2 (4000);
      l_splite                       VARCHAR2 (10) := ‘++‘;
      boundary              CONSTANT VARCHAR2 (256) := ‘-----BYSUK‘;
      first_boundary        CONSTANT VARCHAR2 (256) := ‘--‘ || boundary || l_crlf;
      last_boundary         CONSTANT VARCHAR2 (256)
                                        := ‘--‘ || boundary || ‘--‘ || l_crlf ;
      multipart_mime_type   CONSTANT VARCHAR2 (256)
         := ‘multipart/mixed; boundary="‘ || boundary || ‘"‘ ;

      TYPE address_list IS TABLE OF VARCHAR2 (100)
                              INDEX BY BINARY_INTEGER;

      my_address_list                address_list;

      ---------------------------------------split mail address----------------------------------------------
      PROCEDURE p_splite_str (p_str VARCHAR2, p_splite_flag INT DEFAULT 1)
      IS
         l_addr   VARCHAR2 (254) := ‘‘;
         l_len    INT;
         l_str    VARCHAR2 (4000);
         j        INT := 0;
      BEGIN
         /*Handle recieve mail address, such like blank, semicolon*/
         l_str :=
            TRIM (RTRIM (REPLACE (REPLACE (p_str, ‘;‘, ‘,‘), ‘ ‘, ‘‘), ‘,‘));
         l_len := LENGTH (l_str);

         FOR i IN 1 .. l_len
         LOOP
            IF SUBSTR (l_str, i, 1) <> ‘,‘
            THEN
               l_addr := l_addr || SUBSTR (l_str, i, 1);
            ELSE
               j := j + 1;

               IF p_splite_flag = 1
               THEN
                  --Add  symbol  ‘<>‘  for each mail address. else could not send to many reciever
                  l_addr := ‘<‘ || l_addr || ‘>‘;

                  my_address_list (j) := l_addr;
               END IF;

               l_addr := ‘‘;
            END IF;

            IF i = l_len
            THEN
               j := j + 1;

               IF p_splite_flag = 1
               THEN
                  l_addr := ‘<‘ || l_addr || ‘>‘;
                  my_address_list (j) := l_addr;
               END IF;
            END IF;
         END LOOP;
      END;

      -----------------------------------write mail header and mail content----------------------------------
      PROCEDURE write_data (p_conn     IN OUT NOCOPY UTL_SMTP.connection,
                            p_name     IN            VARCHAR2,
                            p_value    IN            VARCHAR2,
                            p_splite                 VARCHAR2 DEFAULT ‘:‘,
                            p_crlf                   VARCHAR2 DEFAULT l_crlf)
      IS
      BEGIN
         /* utl_raw.cast_to_raw  to handle chinese code*/
         UTL_SMTP.write_raw_data (
            p_conn,
            UTL_RAW.cast_to_raw (
               CONVERT (p_name || p_splite || p_value || p_crlf,
                        ‘ZHS16CGB231280‘)));
      END;

      ----------------------------------------write mime mail tail-----------------------------------------------------
      PROCEDURE end_boundary (conn   IN OUT NOCOPY UTL_SMTP.connection,
                              LAST   IN            BOOLEAN DEFAULT FALSE)
      IS
      BEGIN
         UTL_SMTP.write_data (conn, UTL_TCP.crlf);

         IF (LAST)
         THEN
            UTL_SMTP.write_data (conn, last_boundary);
         END IF;
      END;

      ---------------------------------------------send mail procedure--------------------------------------------
      PROCEDURE p_email (p_sendoraddress2      VARCHAR2,      --sender address
                         p_receiveraddress2    VARCHAR2)    --reciever address
      IS
         l_conn   UTL_SMTP.connection;                   --create a connection
      BEGIN
         /*Initial mail server*/
         l_conn := UTL_SMTP.open_connection (p_server, p_port);
         UTL_SMTP.helo (l_conn, p_server);

         /* smtp authentication*/
         IF p_need_smtp = 1
         THEN
            UTL_SMTP.command (l_conn, ‘AUTH LOGIN‘, ‘‘);
            UTL_SMTP.command (
               l_conn,
               UTL_RAW.cast_to_varchar2 (
                  UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_user))));
            UTL_SMTP.command (
               l_conn,
               UTL_RAW.cast_to_varchar2 (
                  UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (p_pass))));
         END IF;

         /*configure sender and reciever mail address*/
         UTL_SMTP.mail (l_conn, p_sendoraddress2);
         UTL_SMTP.rcpt (l_conn, p_receiveraddress2);
         /*configure mail header*/
         UTL_SMTP.open_data (l_conn);
         /*configure date*/
         --write_data(l_conn, ‘Date‘, to_char(sysdate-1/3, ‘dd Mon yy hh24:mi:ss‘));
         /*configure sender*/
         write_data (l_conn, ‘From‘, p_sendor);
         /*configure reciever*/
         write_data (l_conn, ‘To‘, p_receiver);

         /*add mail subject*/
         SELECT REPLACE (
                   ‘=?GB2312?B?‘
                   || UTL_RAW.cast_to_varchar2 (
                         UTL_ENCODE.base64_encode (RAWTOHEX (p_sub)))
                   || ‘?=‘,
                   UTL_TCP.crlf,
                   ‘‘)
           INTO p_subject
           FROM DUAL;

         write_data (l_conn, ‘Subject‘, p_subject);
         write_data (l_conn, ‘Content-Type‘, multipart_mime_type);
         UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);
         UTL_SMTP.write_data (l_conn, first_boundary);
         write_data (l_conn, ‘Content-Type‘, ‘text/html‘);

         UTL_SMTP.write_data (l_conn, UTL_TCP.crlf);

         write_data (
            l_conn,
            ‘‘,
            REPLACE (REPLACE (p_txt, l_splite, CHR (10)), CHR (10), l_crlf),
            ‘‘,
            ‘‘);
         end_boundary (l_conn);
         /*close write data*/
         UTL_SMTP.close_data (l_conn);
         /*close connection*/
         UTL_SMTP.quit (l_conn);
      END;
   ---------------------------------------------main procedure -----------------------------------------------------
   BEGIN
      err_num := 0;
      l_sendoraddress := ‘<‘ || p_sendor || ‘>‘;
      p_splite_str (p_receiver);                         --handle mail address

      FOR k IN 1 .. my_address_list.COUNT
      LOOP
         p_email (l_sendoraddress, my_address_list (k));
      END LOOP;
   END;
END;
/

PL/SQL 下邮件发送程序    PL/SQL 下邮件发送程序

更多参考

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

使用PL/SQL Developer剖析PL/SQL代码

对比 PL/SQL profiler 剖析结果

PL/SQL Profiler 剖析报告生成html

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

PL/SQL 下邮件发送程序

上一篇:120名商家眼中微信营销的十个真相


下一篇:ORACLE SQL*PLUS环境变量设置及说明