转载

改进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;

正文到此结束