OracleUTL_STMP发送邮件时出现乱码问号解决方案
2023-02-10 11:19阅读：
Oracle UTL_STMP发送邮件时出现乱码问号解决方案
我们使用utl_stmp 发送邮件时 标题会使用以下代码
UTL_SMTP.write_data(conn, 'Subject: ' || subject ||
UTL_TCP.crlf);
这样写对于英文标题是没有问题，但是当标题为中文市，邮件箱的 邮件标题 会出现??????的情况
以下代码用于解决 问号 问题 供参考
ROCEDURE send_mail_demo(subject IN VARCHAR2
,message IN OUT CLOB
,tomail IN
VARCHAR2
,frommail IN VARCHAR2
,fromname IN VARCHAR2
,priority IN INTEGER DEFAULT 3)
IS
priority_high CONSTANT INTEGER := 1;
priority_normal CONSTANT INTEGER := 3;
priority_low CONSTANT INT
EGER := 5;
mime_html CONSTANT VARCHAR2(50) :=
'text/html;';
smtp_port CONSTANT INTEGER := 25;
smtp_server CONSTANT VARCHAR2(50) := 'mailhost';
con utl_smtp.connection;
ret utl_smtp.reply;
charset VARCHAR2(20);
loblen INTEGER;
amount INTEGER := 8000;
buffer VARCHAR2(32000);
offset INTEGER := 1;
BEGIN
-- setup mail header
con := utl_smtp.open_connection(smtp_server
,smtp_port);
ret := utl_smtp.helo(con
,sys_context('USERENV'
,'DB_DOMAIN'));
ret := utl_smtp.mail(con
,frommail);
ret := utl_smtp.rcpt(con
,tomail);
ret := utl_smtp.open_data(con);
IF convert(fromname
,'US7ASCII') =
fromname THEN
utl_smtp.write_data(con
,'From: '' || fromname || '' <'
|| frommail || '>' || utl_tcp.crlf);
ELSE
utl_smtp.write_data(con
,'From: =?UTF-8?B?' ||
utl_encode.text_encode(fromname
,'AL32UTF8'
,utl_encode.base64) || '?= <' || frommail ||
'>' ||
utl_tcp.crlf);
END IF;
utl_smtp.write_data(con
,'To: ' || tomail || utl_tcp.crlf);
IF convert(subject
,'US7ASCII') =
subject THEN
utl_smtp.write_data(con
,'Subject: ' || subject ||
utl_tcp.crlf);
ELSE
utl_smtp.write_data(con
,'Subject: =?UTF-8?B?' ||
REPLACE(REPLACE(utl_encode.text_encode(subject
,'AL32UTF8'
,utl_encode.base64)
,chr(13)
,NULL)
,chr(10)
,NULL) || '?=' ||
utl_tcp.crlf);
END IF;
utl_smtp.write_data(con
,'Date: ' || to_char(current_timestamp
,'Dy, DD Mon YYYY hh24:mi:ss
TZHTZM'
,'NLS_DATE_LANGUAGE =
American') || utl_tcp.crlf);
utl_smtp.write_data(con
,'X-Priority: ' || priority ||
utl_tcp.crlf);
SELECT utl_i18n.map_charset(VALUE) INTO charset FROM
nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
utl_smtp.write_data(con
,'Content-type: ' || mime_html || '
charset=' || charset || utl_tcp.crlf);
-- Mail Body
utl_smtp.write_data(con
,utl_tcp.crlf);
loblen := dbms_lob.getlength(message);
LOOP
EXIT WHEN offset > loblen;
dbms_lob.read(message
,amount
,offset
,buffer);
utl_smtp.write_raw_data(con
,utl_raw.cast_to_raw(buffer));
offset := offset + amount;
END LOOP;
utl_smtp.write_data(con
,utl_tcp.crlf);
-- finish mail
ret := utl_smtp.close_data(con);
ret := utl_smtp.quit(con);
EXCEPTION
WHEN utl_smtp.transient_error
OR utl_smtp.permanent_error
THEN
utl_smtp.quit(con);
END send_mail_demo;
-- 刘轶鹤
