Oracle 10G 使用UTL_SMTP发送中文电子邮件[Z]

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:=‘[email protected]‘; --发件人地址
        P_SMTP_HOSTNAME:=‘smtp.163.com‘; --smtp服务器
        P_SMTP_PORTNUM:=‘25‘;                    --smtp服务器端口号
        L_ACCOUNT:=‘[email protected]‘;--登录服务器用账号
        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; 
/