Oracle UTL_STMP发送邮件时出现乱码问号解决方案

跳跃生命线

已于 2023-02-10 11:19:26 修改

阅读量1.4k

点赞数 2

分类专栏：技术类 #Oracle #PLSQL 文章标签： oracle UTL_STMP发送邮件乱码

于 2023-02-10 11:19:04 首次发布

本文链接：https://blog.csdn.net/AlexLiu_2019/article/details/128966762

版权

技术类同时被 3 个专栏收录

150 篇文章 2 订阅

订阅专栏

#Oracle

50 篇文章 1 订阅

订阅专栏

#PLSQL

7 篇文章 1 订阅

订阅专栏

文章提供了一个OraclePL/SQL过程，该过程详细描述了如何在使用UTL_SMTP发送邮件时处理中文标题的乱码问题。通过检查字符集并使用适当的编码转换，如UTF-8和Base64，确保邮件标题和内容能正确显示中文。

摘要由CSDN通过智能技术生成

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 INTEGER := 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;

-- 刘轶鹤