转载

改进oracle utl_mail包的smtp_server设定和密码验证不足


一.通过utl_smtp发送邮件的范例
DECLARE
  c utl_smtp.connection;
BEGIN
  c := utl_smtp.open_connection('smtp.exmail.qq.com');
  utl_smtp.helo(c, 'smtp.exmail.qq.com');
  utl_smtp.auth(c        => c,
                username => 'selectshen@foxmail.com',
                password => 'mail_password',
                schemes  => 'LOGIN');
  utl_smtp.mail(c, 'selectshen@foxmail.com');
  utl_smtp.rcpt(c, '20084622@qq.com');
  utl_smtp.open_data(c);
  utl_smtp.write_data(c,
                      'From' || ': ' || '' ||
                      utl_tcp.CRLF);
  utl_smtp.write_data(c,
                      'To' || ': ' || '<20084622@qq.com>' ||
                      utl_tcp.CRLF);
  utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(convert('Subject' || ': ' || '来自oracle数据库的邮件' || utl_tcp.CRLF,'ZHS16GBK')));
  UTL_SMTP.WRITE_DATA(c,
                        'Content-Type: '||'text/plain; charset=gb2312'||utl_tcp.CRLF);
  utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(convert( utl_tcp.CRLF || '你好,oracle!','ZHS16GBK')));
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(c);
    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        NULL;
    END;
    raise_application_error(-20000,
                            'Failed to send mail due to the following error: ' ||
                            sqlerrm);
END;

二.改进utl_mail包
oracle从10.1.0.2提供一个ult_mail的包,用于发送邮件,它是对utl_smtp进行了封装,简化发邮件的步骤.
但存在的不足:
1.需要通过alter session或alter system去设定smtp_server
2.无法使用密码验证
通过对utl_mail包进行改进,将smtp_server的设定放在发送邮件的参数中,并在发送的存储过程中加入密码验证,去增强实用性.
发邮件的存储过程使用方法,可参考    How to Use the UTL_MAIL Package (文档 ID 269375.1).
并且要注意,从11g开始,如果不是sys用户去发邮件,要设定ACL,可参考ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later (文档 ID 1209644.1).

下面是简单的调用示例:
#改进后的包名个人定义为utl_email
UTL_EMAIL.SEND发送不含附件的邮件.
例如:
begin
  scott.UTL_EMAIL.SEND(SMTP_SERVER   => 'smtp.exmail.qq.com',
                SENDER        => 'selectshen@foxmail.com',
                AUTH_PASSWORD => 'mail_password',
                recipients    => '20084622@qq.com',
                subject       => '数据库测试',
                message       => '中文',
                mime_type     => 'text/plain; charset=utf8'
                );                
end;

UTL_EMAIL.SEND_ATTACH_VARCHAR2发送包含文本附件的邮件.
例如:
begin
  scott.UTL_EMAIL.SEND_ATTACH_VARCHAR2(SMTP_SERVER   => 'smtp.exmail.qq.com',
                                SENDER        => 'selectshen@foxmail.com',
                                AUTH_PASSWORD => 'mail_password',
                                recipients    => '20084622@qq.com',
                                subject       => 'oracle数据库发送的测试邮件',
                                message       => '你好,oracle!',
                                mime_type     => 'text/plain; charset=utf8',
                                attachment    => '这是附件里的内容',
                                ATT_INLINE    => FALSE,
                                att_filename  =>'a.txt'
                                );
end;

UTL_EMAIL.send_attach_raw发送包含二进制附件的邮件.

以下是改进后的utl_email包的定义,用户只需新建以下包即可:
#PACKAGECREATE OR REPLACE PACKAGE utl_email AUTHID CURRENT_USER AS

  -------------
  --  CONSTANTS
  --
  invalid_argument EXCEPTION;
  invalid_priority EXCEPTION;
  invalid_argument_errcode CONSTANT PLS_INTEGER := -29261;
  PRAGMA EXCEPTION_INIT(invalid_argument, -29261);
  PRAGMA EXCEPTION_INIT(INVALID_PRIORITY, -44101);
  /*----------------------------------------------------------------
  **
  ** SEND - send an email message
  **
  ** This procedure packages and delivers an email message to the
  ** SMTP server specified by the following configuration parameters:
  **
  **   SMTP_SERVER=my_server.my_company.com
  **   SMTP_DOMAIN=my_company.com
  **
  ** SEND PROCEDURE
  ** IN
  **   sender       - sender address
  **   recipients   - address(es) of 1 or more recipients, comma delimited
  **   cc           - CC (carbon copy) recipient(s)), 1 or more addresses,
  **                    comma delimited, default=NULL
  **   bcc          - BCC (blind carbon copy) recipient(s), 1 or more
  **                    addresses, comma delimited, default=NULL
  **   subject      - subject string, default=NULL
  **   message      - message text, default=NULL
  **   mime_type    - mime type, default='text/plain'
  **   priority     - message priority, default=3, valid values are [1..5]
  **
  ** SEND_ATTACH_VARCHAR2 PROCEDURE
  ** IN
  **   sender       - sender address
  **   recipients   - address(es) of 1 or more recipients, comma delimited
  **   cc           - CC (carbon copy) recipient(s)), 1 or more addresses,
  **                    comma delimited, default=NULL
  **   bcc          - BCC (blind carbon copy) recipient(s), 1 or more
  **                    addresses, comma delimited, default=NULL
  **   subject      - subject string, default=NULL
  **   message      - message text, default=NULL
  **   mime_type    - mime type, default='text/plain'
  **   priority     - message priority, default=3, valid values are [1..5]
  **   att_txt_inline - boolean specifying whether the attachment is viewable
  **                    inline with the message body text, default=TRUE
  **   attachment   - attachment text data
  **   att_mime_type- attachment mime_type, default='text/plain'
  **   att_filename - filename to be offered as a default upon saving the
  **                    attachment to disk
  **
  ** SEND_ATTACH_RAW PROCEDURE
  ** IN
  **   sender       - sender address
  **   recipients   - address(es) of 1 or more recipients, comma delimited
  **   cc           - CC (carbon copy) recipient(s)), 1 or more addresses,
  **                    comma delimited, default=NULL
  **   bcc          - BCC (blind carbon copy) recipient(s), 1 or more
  **                    addresses, comma delimited, default=NULL
  **   subject      - subject string, default=NULL
  **   message      - message text, default=NULL
  **   mime_type    - mime type, default='text/plain'
  **   priority     - message priority, default=3, valid values are [1..5]
  **   att_raw_inline - boolean specifying whether the attachment is viewable
  **                    inline with the message body text, default=TRUE
  **   attachment   - attachment RAW data
  **   att_mime_type- attachment mime_type, default='application/octet'
  **   att_filename - filename to be offered as a default upon saving the
  **                    attachment to disk
  **
  */

  PROCEDURE send(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                 SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                 SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                 AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                 recipients    IN VARCHAR2 CHARACTER SET ANY_CS,
                 cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                 priority      IN PLS_INTEGER DEFAULT 3,
                 replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

  PROCEDURE send_attach_varchar2(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                                 SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                                 SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                                 AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                                 recipients    IN VARCHAR2 CHARACTER SET ANY_CS,
                                 cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                                 priority      IN PLS_INTEGER DEFAULT 3,
                                 attachment    IN VARCHAR2 CHARACTER SET ANY_CS,
                                 att_inline    IN BOOLEAN DEFAULT TRUE,
                                 att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                                 att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

  PROCEDURE send_attach_raw(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                            SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                            SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                            AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                            recipients    IN VARCHAR2 CHARACTER SET ANY_CS,
                            cc            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            bcc           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            subject       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            message       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            mime_type     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                            priority      IN PLS_INTEGER DEFAULT 3,
                            attachment    IN RAW,
                            att_inline    IN BOOLEAN DEFAULT TRUE,
                            att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet',
                            att_filename  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            replyto       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

END;
/
#PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY scott.utl_email IS

  LONG_HEADER_FIELD    CONSTANT INTEGER := 65;
  SUBJECT_PIECE_LENGTH CONSTANT INTEGER := 40;

  BOUNDARY CONSTANT VARCHAR2(256) := '------------4D8C24=_23F7E4A13B2357B3';

  BAD_ARGUMENT EXCEPTION;
  PRAGMA EXCEPTION_INIT(BAD_ARGUMENT, -29261);

  FUNCTION LOOKUP_UNQUOTED_CHAR(STR  IN VARCHAR2 CHARACTER SET ANY_CS,
                                CHRS IN VARCHAR2) RETURN PLS_INTEGER AS
    C            VARCHAR2(5) CHARACTER SET STR%CHARSET;
    I            PLS_INTEGER;
    LEN          PLS_INTEGER;
    INSIDE_QUOTE BOOLEAN;
  BEGIN
    INSIDE_QUOTE := FALSE;
    I            := 1;
    LEN          := LENGTH(STR);
    WHILE (I <= LEN) LOOP
    
      C := SUBSTR(STR, I, 1);
    
      IF (INSIDE_QUOTE) THEN
        IF (C = '"') THEN
          INSIDE_QUOTE := FALSE;
        ELSIF (C = '/') THEN
          I := I + 1;
        END IF;
        GOTO NEXT_CHAR;
      END IF;
    
      IF (C = '"') THEN
        INSIDE_QUOTE := TRUE;
        GOTO NEXT_CHAR;
      END IF;
    
      IF (INSTR(CHRS, C) >= 1) THEN
        RETURN I;
      END IF;
    
      <>
      I := I + 1;
    
    END LOOP;
 
    RETURN 0;
 
  END;

  FUNCTION GET_ADDRESS(ADDR_LIST IN OUT VARCHAR2) RETURN VARCHAR2 IS
 
    ADDR VARCHAR2(256);
    I    PLS_INTEGER;
 
  BEGIN
 
    ADDR_LIST := LTRIM(ADDR_LIST);
    I         := LOOKUP_UNQUOTED_CHAR(ADDR_LIST, ',;');
    IF (I >= 1) THEN
      ADDR      := SUBSTR(ADDR_LIST, 1, I - 1);
      ADDR_LIST := SUBSTR(ADDR_LIST, I + 1);
    ELSE
      ADDR      := ADDR_LIST;
      ADDR_LIST := '';
    END IF;
 
    I := LOOKUP_UNQUOTED_CHAR(ADDR, '<');
    IF (I >= 1) THEN
      ADDR := SUBSTR(ADDR, I + 1);
      I    := INSTR(ADDR, '>');
      IF (I >= 1) THEN
        ADDR := SUBSTR(ADDR, 1, I - 1);
      END IF;
    END IF;
    RETURN ADDR;
  END;

  FUNCTION ENCODE_VARCHAR2(DATA IN VARCHAR2 CHARACTER SET ANY_CS)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(DATA)));
  END;

  FUNCTION ENCODE_RAW(DATA IN RAW) RETURN RAW IS
  BEGIN
    RETURN UTL_ENCODE.BASE64_ENCODE(DATA);
  END;

  FUNCTION ENCODE_HEADER(DATA IN VARCHAR2 CHARACTER SET ANY_CS)
    RETURN VARCHAR2 CHARACTER SET DATA%CHARSET IS
  BEGIN
    RETURN(UTL_ENCODE.MIMEHEADER_ENCODE(DATA));
  END;

  FUNCTION ENCODE_RECIPIENTS(RCPTS IN VARCHAR2 CHARACTER SET ANY_CS)
    RETURN VARCHAR2 CHARACTER SET RCPTS%CHARSET IS
    START_LOC           PLS_INTEGER := 1;
    OCCUR_LOC           PLS_INTEGER := 1;
    SINGLE_RCPT         VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
    ENCODED_RCPTS       VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
    REMAINING_RCPTS     VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
    ADDRESS_SEP         VARCHAR2(1) CHARACTER SET RCPTS%CHARSET;
    ENCODED_SINGLE_RCPT VARCHAR2(32767) CHARACTER SET RCPTS%CHARSET;
 
    FUNCTION ENCODE_SINGLE_RCPT(SINGLE_RCPT IN VARCHAR2 CHARACTER SET ANY_CS)
      RETURN VARCHAR2 CHARACTER SET SINGLE_RCPT%CHARSET IS
    
      SEPARATOR_LOC       PLS_INTEGER := 0;
      START_LOC           PLS_INTEGER := 0;
      SINGLE_RCPT_PIECE1  VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
      SINGLE_RCPT_PIECE2  VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
      ENCODED_SINGLE_RCPT VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
      PIECE1_SPLIT        VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
      ENCODED_PIECE       VARCHAR2(32767) CHARACTER SET SINGLE_RCPT%CHARSET;
    
    BEGIN
    
      ENCODED_SINGLE_RCPT := SINGLE_RCPT;
      SEPARATOR_LOC       := LOOKUP_UNQUOTED_CHAR(SINGLE_RCPT, '<');
    
      IF (SEPARATOR_LOC >= 1) THEN
        ENCODED_SINGLE_RCPT := NULL;
        SINGLE_RCPT_PIECE1  := SUBSTR(SINGLE_RCPT, 1, SEPARATOR_LOC - 1);
        SINGLE_RCPT_PIECE2  := SUBSTR(SINGLE_RCPT, SEPARATOR_LOC);
      
        IF (SINGLE_RCPT_PIECE1 IS NOT NULL) THEN
          ENCODED_SINGLE_RCPT := ENCODE_HEADER(SINGLE_RCPT_PIECE1);
        
          IF (LENGTH(ENCODED_SINGLE_RCPT) <= LONG_HEADER_FIELD) THEN
            ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || UTL_TCP.CRLF || ' ';
          ELSE
          
            SEPARATOR_LOC       := 0;
            START_LOC           := 1;
            ENCODED_SINGLE_RCPT := NULL;
            LOOP
              SEPARATOR_LOC := INSTR(SINGLE_RCPT_PIECE1, ' ', START_LOC);
              EXIT WHEN(SEPARATOR_LOC = 0);
              PIECE1_SPLIT := SUBSTR(SINGLE_RCPT_PIECE1,
                                     START_LOC,
                                     SEPARATOR_LOC - START_LOC + 1);
              START_LOC    := SEPARATOR_LOC + 1;
              IF (PIECE1_SPLIT IS NOT NULL) THEN
                ENCODED_PIECE := ENCODE_HEADER(PIECE1_SPLIT);
                IF (LENGTH(ENCODED_PIECE) > LONG_HEADER_FIELD) THEN
                
                  GOTO REPORT_ERROR;
                END IF;
                ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || ENCODED_PIECE ||
                                       UTL_TCP.CRLF || ' ';
              END IF;
            END LOOP;
          
            PIECE1_SPLIT := SUBSTR(SINGLE_RCPT_PIECE1,
                                   START_LOC,
                                   LENGTH(SINGLE_RCPT_PIECE1) - START_LOC + 1);
            IF (PIECE1_SPLIT IS NOT NULL) THEN
              ENCODED_PIECE := ENCODE_HEADER(PIECE1_SPLIT);
            
              IF (LENGTH(ENCODED_PIECE) > LONG_HEADER_FIELD) THEN
              
                GOTO REPORT_ERROR;
              END IF;
              ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || ENCODED_PIECE ||
                                     UTL_TCP.CRLF || ' ';
            END IF;
          END IF;
        END IF;
      
        IF (LENGTH(SINGLE_RCPT_PIECE2) > LONG_HEADER_FIELD) THEN
          GOTO REPORT_ERROR;
        END IF;
        ENCODED_SINGLE_RCPT := ENCODED_SINGLE_RCPT || SINGLE_RCPT_PIECE2;
      END IF;
    
      RETURN ENCODED_SINGLE_RCPT;
    
      <>
      RAISE BAD_ARGUMENT;
    END;
 
  BEGIN
    ENCODED_RCPTS   := NULL;
    REMAINING_RCPTS := RCPTS;
    LOOP
    
      OCCUR_LOC := LOOKUP_UNQUOTED_CHAR(REMAINING_RCPTS, ';,');
      EXIT WHEN(OCCUR_LOC = 0);
      SINGLE_RCPT := SUBSTR(REMAINING_RCPTS, 1, OCCUR_LOC - 1);
      ADDRESS_SEP := SUBSTR(REMAINING_RCPTS, OCCUR_LOC, 1);
    
      REMAINING_RCPTS := SUBSTR(REMAINING_RCPTS, OCCUR_LOC + 1);
    
      IF (SINGLE_RCPT IS NOT NULL) THEN
      
        ENCODED_SINGLE_RCPT := ENCODE_SINGLE_RCPT(SINGLE_RCPT);
        ENCODED_RCPTS       := ENCODED_RCPTS || ENCODED_SINGLE_RCPT ||
                               ADDRESS_SEP || UTL_TCP.CRLF || ' ';
      END IF;
    END LOOP;
 
    IF (REMAINING_RCPTS IS NOT NULL) THEN
      ENCODED_SINGLE_RCPT := ENCODE_SINGLE_RCPT(REMAINING_RCPTS);
      ENCODED_RCPTS       := ENCODED_RCPTS || ENCODED_SINGLE_RCPT;
    END IF;
    RETURN ENCODED_RCPTS;
  END;

  PROCEDURE SEND_I(SMTP_SERVER    IN VARCHAR2 CHARACTER SET ANY_CS,
                   SMTP_PORT      IN PLS_INTEGER,
                   SENDER         IN VARCHAR2 CHARACTER SET ANY_CS,
                   AUTH_PASSWORD  IN VARCHAR2 CHARACTER SET ANY_CS,
                   RECIPIENTS     IN VARCHAR2 CHARACTER SET ANY_CS,
                   CC             IN VARCHAR2 CHARACTER SET ANY_CS,
                   BCC            IN VARCHAR2 CHARACTER SET ANY_CS,
                   SUBJECT        IN VARCHAR2 CHARACTER SET ANY_CS,
                   MESSAGE        IN VARCHAR2 CHARACTER SET ANY_CS,
                   MIME_TYPE      IN VARCHAR2 CHARACTER SET ANY_CS,
                   PRIORITY       IN PLS_INTEGER,
                   TXT_ATTACHMENT IN VARCHAR2 CHARACTER SET ANY_CS,
                   RAW_ATTACHMENT IN RAW,
                   ATT_MIME_TYPE  IN VARCHAR2 CHARACTER SET ANY_CS,
                   ATT_INLINE     IN BOOLEAN,
                   ATT_FILENAME   IN VARCHAR2 CHARACTER SET ANY_CS,
                   REPLYTO        IN VARCHAR2 CHARACTER SET ANY_CS) IS
 
    MAIL_CONN     UTL_SMTP.CONNECTION;
    CRLF          VARCHAR2(10) := UTL_TCP.CRLF;
    ATTACH_FLAG   PLS_INTEGER := 0;
    TEXT_TYPE     NUMBER := 1;
    RAW_TYPE      NUMBER := 2;
    SUBJECT_TEMP  VARCHAR2(32767);
    NONE_TYPE     NUMBER := 0;
    SENDER_COPY   VARCHAR2(32767) := SENDER;
    PRIORITY_COPY PLS_INTEGER := PRIORITY;
    ALL_RCPTS     VARCHAR2(32767) := RECIPIENTS;
 
    CONNECTION_OPENED BOOLEAN := FALSE;
  BEGIN
 
    IF (TXT_ATTACHMENT IS NOT NULL) THEN
      ATTACH_FLAG := TEXT_TYPE;
    ELSE
      IF (RAW_ATTACHMENT IS NOT NULL) THEN
        ATTACH_FLAG := RAW_TYPE;
      END IF;
    END IF;
 
    MAIL_CONN         := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    CONNECTION_OPENED := TRUE;
 
    UTL_SMTP.HELO(MAIL_CONN, SMTP_SERVER);

    if (AUTH_PASSWORD is not null) then
      utl_smtp.auth(c        => MAIL_CONN,
                    username => SENDER_COPY,
                    password => AUTH_PASSWORD,
                    schemes  => 'LOGIN');
    end if;
 
    UTL_SMTP.MAIL(MAIL_CONN, SENDER_COPY);
 
    IF (CC IS NOT NULL) THEN
      ALL_RCPTS := ALL_RCPTS || ', ' || CC;
    END IF;
    IF (BCC IS NOT NULL) THEN
      ALL_RCPTS := ALL_RCPTS || ', ' || BCC;
    END IF;
 
    WHILE (ALL_RCPTS IS NOT NULL) LOOP
      UTL_SMTP.RCPT(MAIL_CONN, '<' || GET_ADDRESS(ALL_RCPTS) || '>');
    END LOOP;
 
    UTL_SMTP.OPEN_DATA(MAIL_CONN);
 
    IF (SENDER IS NOT NULL) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'From: ' || ENCODE_RECIPIENTS(SENDER) || CRLF);
    ELSE
      RAISE BAD_ARGUMENT;
    END IF;
 
    IF (RECIPIENTS IS NOT NULL) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'To: ' || ENCODE_RECIPIENTS(RECIPIENTS) || CRLF);
    ELSE
      RAISE BAD_ARGUMENT;
    END IF;
 
    IF (CC IS NOT NULL) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'CC: ' || ENCODE_RECIPIENTS(CC) || CRLF);
    END IF;
 
    IF (REPLYTO IS NOT NULL) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'Reply-To: ' || ENCODE_RECIPIENTS(REPLYTO) || CRLF);
    END IF;
 
    UTL_SMTP.WRITE_DATA(MAIL_CONN,
                        'Orig-Date: ' ||
                        TO_CHAR(CURRENT_TIMESTAMP,
                                'Dy Mon YYYY HH24:MI:SS TZHTZM') || CRLF);
 
    SUBJECT_TEMP := SUBJECT;
 
    DECLARE
      CURRENTPIECE    VARCHAR2(32767) := NULL;
      ENCODEDPIECE    VARCHAR2(32767) := NULL;
      PIECELENGTH     NUMBER := SUBJECT_PIECE_LENGTH;
      REMAININGLENGTH NUMBER := LENGTH(SUBJECT_TEMP);
      FIRSTPIECE      BOOLEAN := TRUE;
    BEGIN
    
      WHILE (REMAININGLENGTH > 0) LOOP
      
        CURRENTPIECE := SUBSTR(SUBJECT_TEMP, 1, PIECELENGTH);
      
        ENCODEDPIECE := ENCODE_HEADER(CURRENTPIECE);
      
        IF (LENGTH(ENCODEDPIECE) <= LONG_HEADER_FIELD) THEN
          IF (FIRSTPIECE = FALSE) THEN
            UTL_SMTP.WRITE_DATA(MAIL_CONN, ' ' || ENCODEDPIECE || CRLF);
          ELSE
          
            UTL_SMTP.WRITE_DATA(MAIL_CONN,
                                'Subject: ' || ENCODEDPIECE || CRLF);
            FIRSTPIECE := FALSE;
          END IF;
        
          SUBJECT_TEMP    := SUBSTR(SUBJECT_TEMP,
                                    PIECELENGTH + 1,
                                    REMAININGLENGTH);
          REMAININGLENGTH := LENGTH(SUBJECT_TEMP);
        
          PIECELENGTH := SUBJECT_PIECE_LENGTH;
        ELSE
        
          PIECELENGTH := PIECELENGTH / 2;
        
          IF (PIECELENGTH = 0) THEN
            RAISE BAD_ARGUMENT;
          END IF;
        END IF;
      END LOOP;
    END;
 
    IF (PRIORITY IS NOT NULL) THEN
      IF ((PRIORITY > 5) OR (PRIORITY < 1)) THEN
        RAISE INVALID_PRIORITY;
      END IF;
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'X-Priority: ' || PRIORITY_COPY || CRLF);
    END IF;
 
    IF (ATTACH_FLAG > NONE_TYPE) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'Content-Type: multipart/mixed;' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          ' boundary="' || BOUNDARY || '"' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'This is a multi-part message in MIME format.' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || CRLF);
    END IF;
 
    UTL_SMTP.WRITE_DATA(MAIL_CONN, 'Content-Type: ' || MIME_TYPE || CRLF);
 
    IF ((MESSAGE IS NULL) OR (INSTR(UPPER(MIME_TYPE), 'CHARSET') = 0) OR
       (INSTR(UPPER(MIME_TYPE), 'US-ASCII') != 0)) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'Content-Transfer-Encoding: 7bit' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
    
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          NVL(MESSAGE, ' ') || CRLF || CRLF || CRLF);
    ELSE
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'Content-Transfer-Encoding: quoted-printable' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
    
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          ENCODE_VARCHAR2(MESSAGE) || CRLF || CRLF || CRLF);
    END IF;
 
    IF (ATTACH_FLAG > NONE_TYPE) THEN
      UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          'Content-Type: ' || ATT_MIME_TYPE || ';' || CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN,
                          ' name="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF);
    
      IF (ATTACH_FLAG = TEXT_TYPE) THEN
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            'Content-Transfer-Encoding: quoted-printable' || CRLF);
      ELSE
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            'Content-Transfer-Encoding: base64' || CRLF);
      END IF;
    
      IF (ATT_INLINE) THEN
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            'Content-Disposition: inline;' || CRLF);
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            ' filename="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF || CRLF || CRLF);
      ELSE
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            'Content-Disposition: attachment;' || CRLF);
        UTL_SMTP.WRITE_DATA(MAIL_CONN,
                            ' filename="' || NVL(ATT_FILENAME, ' ') || '"' || CRLF || CRLF || CRLF);
      END IF;
    
      IF (ATTACH_FLAG = TEXT_TYPE) THEN
        UTL_SMTP.WRITE_DATA(MAIL_CONN, ENCODE_VARCHAR2(TXT_ATTACHMENT));
      ELSE
        UTL_SMTP.WRITE_RAW_DATA(MAIL_CONN, ENCODE_RAW(RAW_ATTACHMENT));
      END IF;
    
      UTL_SMTP.WRITE_DATA(MAIL_CONN, CRLF);
      UTL_SMTP.WRITE_DATA(MAIL_CONN, '--' || BOUNDARY || '--' || CRLF);
    
    END IF;
 
    UTL_SMTP.CLOSE_DATA(MAIL_CONN);
    UTL_SMTP.QUIT(MAIL_CONN);
 
  EXCEPTION
    WHEN OTHERS THEN
      IF (CONNECTION_OPENED) THEN
        UTL_SMTP.CLOSE_CONNECTION(MAIL_CONN);
      END IF;
      RAISE;
  END;

  PROCEDURE SEND(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                 SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                 SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                 AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                 RECIPIENTS    IN VARCHAR2 CHARACTER SET ANY_CS,
                 CC            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 BCC           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 SUBJECT       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 MESSAGE       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                 MIME_TYPE     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                 PRIORITY      IN PLS_INTEGER DEFAULT 3,
                 REPLYTO       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
  BEGIN
    SEND_I(SMTP_SERVER,
           SMTP_PORT,
           SENDER,
           AUTH_PASSWORD,
           RECIPIENTS,
           CC,
           BCC,
           SUBJECT,
           MESSAGE,
           MIME_TYPE,
           PRIORITY,
           NULL,
           NULL,
           NULL,
           NULL,
           NULL,
           REPLYTO);
  END;

  PROCEDURE SEND_ATTACH_VARCHAR2(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                                 SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                                 SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                                 AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                                 RECIPIENTS    IN VARCHAR2 CHARACTER SET ANY_CS,
                                 CC            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 BCC           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 SUBJECT       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 MESSAGE       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 MIME_TYPE     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                                 PRIORITY      IN PLS_INTEGER DEFAULT 3,
                                 ATTACHMENT    IN VARCHAR2 CHARACTER SET ANY_CS,
                                 ATT_INLINE    IN BOOLEAN DEFAULT TRUE,
                                 ATT_MIME_TYPE IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                                 ATT_FILENAME  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                                 REPLYTO       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
  BEGIN
    SEND_I(SMTP_SERVER,
           SMTP_PORT,
           SENDER,
           AUTH_PASSWORD,
           RECIPIENTS,
           CC,
           BCC,
           SUBJECT,
           MESSAGE,
           MIME_TYPE,
           PRIORITY,
           ATTACHMENT,
           NULL,
           ATT_MIME_TYPE,
           ATT_INLINE,
           ATT_FILENAME,
           REPLYTO);
  END;

  PROCEDURE SEND_ATTACH_RAW(SMTP_SERVER   IN VARCHAR2 CHARACTER SET ANY_CS,
                            SMTP_PORT     IN PLS_INTEGER DEFAULT 25,
                            SENDER        IN VARCHAR2 CHARACTER SET ANY_CS,
                            AUTH_PASSWORD IN VARCHAR2 CHARACTER SET ANY_CS,
                            RECIPIENTS    IN VARCHAR2 CHARACTER SET ANY_CS,
                            CC            IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            BCC           IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            SUBJECT       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            MESSAGE       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            MIME_TYPE     IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii',
                            PRIORITY      IN PLS_INTEGER DEFAULT 3,
                            ATTACHMENT    IN RAW,
                            ATT_INLINE    IN BOOLEAN DEFAULT TRUE,
                            ATT_MIME_TYPE IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'application/octet',
                            ATT_FILENAME  IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
                            REPLYTO       IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL) IS
  BEGIN
    SEND_I(SMTP_SERVER,
           SMTP_PORT,
           SENDER,
           AUTH_PASSWORD,
           RECIPIENTS,
           CC,
           BCC,
           SUBJECT,
           MESSAGE,
           MIME_TYPE,
           PRIORITY,
           NULL,
           ATTACHMENT,
           ATT_MIME_TYPE,
           ATT_INLINE,
           ATT_FILENAME,
           REPLYTO);
  END;

END;


  
正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Java设计模式 Jenkins进阶系列 openfire参考指南 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 Ant教程 java实例教程 SpringCloud Elastic-Job-Lite XStream教程 深入浅出MyBatis Hazelcast教程 ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 springcloud-demo JPA教程 ActiveMQ中文指南 Java内存模型 java-demo dubbo教程 Linux入门视频教程
近期评论
  1. 1 Spring Cloud Circuit Breaker快速入门Demo
  2. 2 Spring Cloud Bus快速入门Demo
  3. 3 Spring Cloud Config快速入门Demo
  4. 4 Spring Cloud Function快速入门Demo
  5. 5 SSPanel-Uim搭建教程 2023年全新定制版主题-解决老版本命令失效问题
  6. 6 Spring Boot集成Milvus和deeplearning4j实现图搜图功能
  7. 7 Spring Boot集成Aviator实现参数校验
  8. 8 ACME自动申请免费的通配符https域名证书
  9. 9 wordpress 数据库和文件优化
  10. 10 CentOS安装Docker与Docker-Compose
网站信息
  • 文章总数：82,696 篇
  • 文件总数：284,257 个
  • 标签总数：2,380 个
  • 分类总数：84 个
  • 留言数量：2,560 条
  • 在线人数：637 人
  • 运行天数：4,392天
  • 最后更新：2024年11月05日22点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG