PL/SQL 下邮件发送程序

最新推荐文章于 2024-08-18 00:29:24 发布

yuanluoliyue_feng

最新推荐文章于 2024-08-18 00:29:24 发布

阅读量695

点赞数

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

1、调用SENDMAIL_PKG来发送邮件

[sql]view plaincopyprint? 
   
 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、邮件发送结果

3、原代码

[sql]view plaincopyprint? 
   
 --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;  
 /  

更多参考

使用 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记录

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

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

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

yuanluoliyue_feng

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL 下邮件发送程序

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

扫一扫