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

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