转载

改进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教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：637 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日21点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG