CREATE OR REPLACE PROCEDURE SCOTT.HTML_EMAIL(

        P_TO                    IN   VARCHAR2, --收件人地址

        P_SUBJECT               IN   VARCHAR2,    --邮件主题

        P_HTML                  IN   VARCHAR2   DEFAULT   NULL--邮件内容支持HTML代码

        )

IS

        L_BOUNDARY           VARCHAR2(255)   DEFAULT   'a1b2c3d4e3f2g1';

        L_CONNECTION         UTL_SMTP.CONNECTION;

        L_BODY_HTML          CLOB   :=   EMPTY_CLOB;     --THIS   LOB   WILL   BE   THE   EMAIL   MESSAGE

        L_OFFSET             NUMBER;

        L_AMMOUNT            NUMBER;

        L_TEMP               VARCHAR2(32767)   DEFAULT   NULL;

        L_ACCOUNT               VARCHAR2(100);

        L_PASSWORD          VARCHAR2(100);

        P_FROM                  VARCHAR2(200);

        P_TEXT                  VARCHAR2(200);

        P_SMTP_HOSTNAME         VARCHAR2(200);

        P_SMTP_PORTNUM         VARCHAR2(200);

BEGIN 

        P_FROM:='test@163.com'; --发件人地址

        P_SMTP_HOSTNAME:='smtp.163.com'; --smtp服务器

        P_SMTP_PORTNUM:='25';                    --smtp服务器端口号

        L_ACCOUNT:='test@163.com';--登录服务器用账号

        L_PASSWORD:='123456';                    --登录服务器密码

         P_TEXT:='there is no text';

        L_CONNECTION   :=   UTL_SMTP.OPEN_CONNECTION(P_SMTP_HOSTNAME,25);

        UTL_SMTP.ehLO(L_CONNECTION,P_SMTP_HOSTNAME); --问候服务器，注意此用用了EHLO，没有使用HELO

        --输入用户名密码

        utl_smtp.command(l_connection,   'AUTH   LOGIN ');

        utl_smtp.command(l_connection, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(L_ACCOUNT))));

        utl_smtp.command(l_connection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(L_PASSWORD)))); 

        UTL_SMTP.MAIL(L_CONNECTION, '<' || P_FROM|| '>'); --发件人信息

        UTL_SMTP.RCPT(L_CONNECTION, '<' || P_TO|| '>'); --收件人地址

        --记得在地址两边加<>，否则可能会报错

        L_TEMP   :=   L_TEMP   ||   'MIME-Version:   1.0 '   ||     CHR(13)   ||   CHR(10);

        L_TEMP   :=   L_TEMP   ||   'To:   '   ||   P_TO   ||   CHR(13)   ||   CHR(10);

        L_TEMP   :=   L_TEMP   ||   'From:   '   ||   P_FROM   ||   CHR(13)   ||   CHR(10);

        L_TEMP   :=   L_TEMP   ||   'Subject:   '   ||   P_SUBJECT   ||   CHR(13)   ||   CHR(10);

        L_TEMP   :=   L_TEMP   ||   'Reply-To:   '   ||   P_FROM   ||     CHR(13)   ||   CHR(10);

        L_TEMP   :=   L_TEMP   ||   'Content-Type:   multipart/alternative;   boundary= '|| CHR(34)||L_BOUNDARY||CHR(34)||CHR(13)||CHR(10); 

        ----------------------------------------------------

        --   WRITE   THE   HEADERS --写入头信息

        DBMS_LOB.CREATETEMPORARY(L_BODY_HTML,FALSE,10);

        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),1,L_TEMP); 

--        ----------------------------------------------------

--        --   WRITE   THE   TEXT   BOUNDARY

--        L_OFFSET:=DBMS_LOB.GETLENGTH(L_BODY_HTML) +1;

--        L_TEMP:= CHR(13)||CHR(10)||'-- '||L_BOUNDARY||CHR(13)||CHR(10);

--        L_TEMP:= L_TEMP||'Content-Type:text/plain;charset=us-ascii'||CHR(13)||CHR(10)||CHR(13)||CHR(10);

--        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP); 

--        ----------------------------------------------------

--        --   WRITE   THE   PLAIN   TEXT   PORTION   OF   THE   EMAIL

--        L_OFFSET   :=   DBMS_LOB.GETLENGTH(L_BODY_HTML)   +   1;

--        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(P_TEXT),L_OFFSET,P_TEXT); 

        ----------------------------------------------------

        --   WRITE   THE   HTML   BOUNDARY

        L_TEMP := CHR(13)||CHR(10)||CHR(13)||CHR(10)|| '--' ||L_BOUNDARY|| CHR(13)||CHR(10);

        L_TEMP := L_TEMP||'Content-Type:text/html; '|| CHR(13)||CHR(10)||CHR(13)||CHR(10);

        L_OFFSET   :=   DBMS_LOB.GETLENGTH(L_BODY_HTML)   +   1;

        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP); 

        ----------------------------------------------------

        --   WRITE   THE   HTML   PORTION   OF   THE   MESSAGE

        L_OFFSET   :=   DBMS_LOB.GETLENGTH(L_BODY_HTML)   +   1;

        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(P_HTML),L_OFFSET,P_HTML); 

        ----------------------------------------------------

        --   WRITE   THE   test vale

--        L_TEMP :='我是中国人';

--        L_OFFSET   :=   DBMS_LOB.GETLENGTH(L_BODY_HTML)   +   1;

--        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP); 

        ----------------------------------------------------

        --   WRITE   THE   FINAL   HTML   BOUNDARY

--        L_TEMP  :=CHR(13)||CHR(10) ||CHR(13)||CHR(10) || '-- '|| L_BOUNDARY|| '-- '||CHR(13);

--        L_OFFSET :=DBMS_LOB.GETLENGTH(L_BODY_HTML) + 1;

--        DBMS_LOB.WRITE(L_BODY_HTML,LENGTH(L_TEMP),L_OFFSET,L_TEMP); 

        ----------------------------------------------------

        --   SEND   THE   EMAIL   IN   1900   BYTE   CHUNKS   TO   UTL_SMTP

        L_OFFSET:=   1;

        L_AMMOUNT:=   1900;

        UTL_SMTP.OPEN_DATA(L_CONNECTION);

        WHILE   L_OFFSET   <   DBMS_LOB.GETLENGTH(L_BODY_HTML)   LOOP

                UTL_SMTP.WRITE_RAW_DATA(L_CONNECTION,UTL_RAW.cast_to_raw(DBMS_LOB.SUBSTR(L_BODY_HTML,L_AMMOUNT,L_OFFSET)));

                L_OFFSET:= L_OFFSET+L_AMMOUNT ;

                L_AMMOUNT:= LEAST(1900,DBMS_LOB.GETLENGTH(L_BODY_HTML)- L_AMMOUNT);

        END   LOOP; 

      --  UTL_SMTP.write_raw_data(L_CONNECTION, UTL_RAW.cast_to_raw('我是中国人'));

        UTL_SMTP.CLOSE_DATA(L_CONNECTION);

        UTL_SMTP.QUIT(L_CONNECTION);

       -- dbms_output.put_line(DBMS_LOB.SUBSTR(L_BODY_HTML,DBMS_LOB.GETLENGTH(L_BODY_HTML),1));

        DBMS_LOB.FREETEMPORARY(L_BODY_HTML);

        dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||'发送邮件成功！！！');

exception

  when others then

  dbms_output.put_line(dbms_utility.format_error_stack);

  dbms_output.put_line(dbms_utility.format_call_stack);

  dbms_output.put_line(sqlerrm);

END;

/