Note that the original SMTP protocol communicates using 7-bit ASCII. Using UTL_ SMTP, all text data (in other words, those in VARCHAR2) will be converted to US7ASCII before it is sent over the wire to the server. Some implementations of SMTP servers that support SMTP extension 8BITMIME [RFC1652] support full 8-bit communication between client and server. 这是关于UTL_SMTP的一个说明,在Oracle8i Supplied PL/SQL Packages Reference Release 2 (8.1.6) 里的,看样子是不行了,不支持中文字符,搂住想别的办法把!
是oracle的问题 解决办法: CREATE OR REPLACE PROCEDURE send_mail ( i_from in varchar2, i_to in varchar2, i_cc in varchar2, i_bcc in varchar2, i_subject in varchar2, i_content in varchar2, i_smtp_server in varchar2, i_port in number := 25, i_user in varchar2 := null, i_pwd in varchar2 := null) is c utl_smtp.connection; v_tmp varchar2(256); v_adr varchar2(256); v_from varchar2(256); v_to varchar2(256); v_cc varchar2(256); v_bcc varchar2(256); -- encodeBase64 function EncodeBase64(i_str varchar2) return varchar2 is begin return twd_base64.encode( utl_raw.cast_to_raw(i_str) ); end; -- EncodeAddress function EncodeAddress(i_str varchar2) return varchar2 is begin return ('=?GB2312?B?'||EncodeBase64(i_str)||'?='); end; -- getAddress function getAddress(x_addr in out varchar2, x_addr_encoded in out varchar2) return varchar2 is v_addr varchar2(256); v_addr_encoded varchar2(256); v_separator varchar2(5); i pls_integer; function lookup_unquoted_char(li_str varchar2, li_chrs varchar2) return pls_integer as c varchar2(5); k pls_integer; l pls_integer; inside_quote boolean; begin inside_quote := false; k := 1; l := length(li_str); while (k <= l) loop c := substr(li_str, k, 1); if (inside_quote) then if (c = '"') then inside_quote := false; elsif (c = '\') then k := k + 1; -- skip the quote character end if; goto next_char; end if; if (c = '"') then inside_quote := true; goto next_char; end if; if (instr(li_chrs, c) >= 1) then return k; end if; <<next_char>> k := k + 1; end loop; return 0; end; begin x_addr := ltrim(x_addr); i := lookup_unquoted_char(x_addr, ',;'); if (i >= 1) then v_separator := substr(x_addr, i, 1); v_addr := substr(x_addr, 1, i - 1); x_addr := substr(x_addr, i + 1); else v_addr := x_addr; x_addr := ''; end if; i := lookup_unquoted_char(v_addr, '<'); if (i >= 1) then v_addr_encoded := EncodeAddress(substr(v_addr, 1, i - 1)) || substr(v_addr, i); v_addr := substr(v_addr, i + 1); i := instr(v_addr, '>'); if (i >= 1) then v_addr := substr(v_addr, 1, i - 1); end if; else v_addr_encoded := v_addr; end if; x_addr_encoded := x_addr_encoded || v_addr_encoded || v_separator; return v_addr; end getAddress; BEGIN c := utl_smtp.open_connection(i_smtp_server, i_port); utl_smtp.helo(c, i_smtp_server); -- 校验认证 if i_user is not null then utl_smtp.command(c, 'auth login', ''); utl_smtp.command(c, EncodeBase64(i_user), ''); utl_smtp.command(c, EncodeBase64(i_pwd), ''); end if; -- 解析 发信人 v_tmp := i_from; v_adr := getAddress(v_tmp, v_from); utl_smtp.mail(c, v_adr); -- 解析 收件人列表 v_tmp := i_to; while (v_tmp is not null) loop v_adr := getAddress(v_tmp, v_to); utl_smtp.rcpt(c, v_adr); end loop; -- 解析 抄送人列表 v_tmp := i_cc; while (v_tmp is not null) loop v_adr := getAddress(v_tmp, v_cc); utl_smtp.rcpt(c, v_adr); end loop; -- 解析 暗送人列表 v_tmp := i_bcc; while (v_tmp is not null) loop v_adr := getAddress(v_tmp, v_bcc); utl_smtp.rcpt(c, v_adr); end loop; utl_smtp.open_data(c); utl_smtp.write_data(c, 'From: '||v_from||utl_tcp.CRLF); utl_smtp.write_data(c, 'To: '||v_to||utl_tcp.CRLF); utl_smtp.write_data(c, 'Cc: '||v_cc||utl_tcp.CRLF); utl_smtp.write_data(c, 'Subject: '||EncodeAddress(i_subject)||utl_tcp.CRLF); utl_smtp.write_data(c, 'Mime-Version: 1.0'||utl_tcp.CRLF); utl_smtp.write_data(c, 'Content-Type: text/plain; charset="GB2312"'||utl_tcp.CRLF); utl_smtp.write_data(c, utl_tcp.CRLF); -- data utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(i_content)); utl_smtp.write_data(c, utl_tcp.CRLF); utl_smtp.close_data(c); utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN utl_smtp.quit(c); raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); END send_mail;
建包:CREATE OR REPLACE PACKAGE twd_base64 IS PROCEDURE init_map ; FUNCTION encode(r IN RAW) RETURN VARCHAR2; END; CREATE OR REPLACE PACKAGE BODY twd_base64 IS
FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS i pls_integer; x pls_integer; y pls_integer; v VARCHAR2(32767); BEGIN
-- For every 3 bytes, split them into 4 6-bit units and map them to -- the Base64 characters i := 1; WHILE ( i + 2 <= utl_raw.length(r) ) LOOP x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 + to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 + to_number(utl_raw.substr(r, i + 2, 1), '0X'); y := floor(x / 262144); v := v || map(y); x := x - y * 262144; y := floor(x / 4096); v := v || map(y); x := x - y * 4096; y := floor(x / 64); v := v || map(y); x := x - y * 64; v := v || map(x); i := i + 3; END LOOP; -- Process the remaining bytes that has fewer than 3 bytes. IF ( utl_raw.length(r) - i = 0) THEN x := to_number(utl_raw.substr(r, i, 1), '0X'); y := floor(x / 4); v := v || map(y); x := x - y * 4; x := x * 16; v := v || map(x); v := v || '=='; ELSIF ( utl_raw.length(r) - i = 1) THEN x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 + to_number(utl_raw.substr(r, i + 1, 1), '0X'); y := floor(x / 1024); v := v || map(y); x := x - y * 1024; y := floor(x / 16); v := v || map(y); x := x - y * 16; x := x * 4; v := v || map(x); v := v || '='; END IF;
SMTP, all text data (in other words, those in VARCHAR2) will be converted to
US7ASCII before it is sent over the wire to the server. Some implementations of
SMTP servers that support SMTP extension 8BITMIME [RFC1652] support full 8-bit
communication between client and server.
这是关于UTL_SMTP的一个说明,在Oracle8i Supplied PL/SQL Packages Reference Release 2 (8.1.6) 里的,看样子是不行了,不支持中文字符,搂住想别的办法把!
解决办法:
CREATE OR REPLACE PROCEDURE send_mail
( i_from in varchar2, i_to in varchar2, i_cc in varchar2, i_bcc in varchar2,
i_subject in varchar2, i_content in varchar2,
i_smtp_server in varchar2,
i_port in number := 25, i_user in varchar2 := null, i_pwd in varchar2 := null)
is
c utl_smtp.connection;
v_tmp varchar2(256);
v_adr varchar2(256);
v_from varchar2(256);
v_to varchar2(256);
v_cc varchar2(256);
v_bcc varchar2(256);
-- encodeBase64
function EncodeBase64(i_str varchar2) return varchar2 is
begin
return twd_base64.encode( utl_raw.cast_to_raw(i_str) );
end;
-- EncodeAddress
function EncodeAddress(i_str varchar2) return varchar2 is
begin
return ('=?GB2312?B?'||EncodeBase64(i_str)||'?=');
end;
-- getAddress
function getAddress(x_addr in out varchar2, x_addr_encoded in out varchar2)
return varchar2 is
v_addr varchar2(256);
v_addr_encoded varchar2(256);
v_separator varchar2(5);
i pls_integer;
function lookup_unquoted_char(li_str varchar2, li_chrs varchar2) return pls_integer as
c varchar2(5);
k pls_integer;
l pls_integer;
inside_quote boolean;
begin
inside_quote := false;
k := 1;
l := length(li_str);
while (k <= l)
loop
c := substr(li_str, k, 1);
if (inside_quote) then
if (c = '"') then
inside_quote := false;
elsif (c = '\') then
k := k + 1; -- skip the quote character
end if;
goto next_char;
end if;
if (c = '"') then
inside_quote := true;
goto next_char;
end if;
if (instr(li_chrs, c) >= 1) then
return k;
end if;
<<next_char>>
k := k + 1;
end loop;
return 0;
end;
begin
x_addr := ltrim(x_addr);
i := lookup_unquoted_char(x_addr, ',;');
if (i >= 1) then
v_separator := substr(x_addr, i, 1);
v_addr := substr(x_addr, 1, i - 1);
x_addr := substr(x_addr, i + 1);
else
v_addr := x_addr;
x_addr := '';
end if;
i := lookup_unquoted_char(v_addr, '<');
if (i >= 1) then
v_addr_encoded := EncodeAddress(substr(v_addr, 1, i - 1)) || substr(v_addr, i);
v_addr := substr(v_addr, i + 1);
i := instr(v_addr, '>');
if (i >= 1) then
v_addr := substr(v_addr, 1, i - 1);
end if;
else
v_addr_encoded := v_addr;
end if;
x_addr_encoded := x_addr_encoded || v_addr_encoded || v_separator;
return v_addr;
end getAddress;
BEGIN
c := utl_smtp.open_connection(i_smtp_server, i_port);
utl_smtp.helo(c, i_smtp_server); -- 校验认证
if i_user is not null then
utl_smtp.command(c, 'auth login', '');
utl_smtp.command(c, EncodeBase64(i_user), '');
utl_smtp.command(c, EncodeBase64(i_pwd), '');
end if; -- 解析 发信人
v_tmp := i_from;
v_adr := getAddress(v_tmp, v_from);
utl_smtp.mail(c, v_adr); -- 解析 收件人列表
v_tmp := i_to;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_to);
utl_smtp.rcpt(c, v_adr);
end loop;
-- 解析 抄送人列表
v_tmp := i_cc;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_cc);
utl_smtp.rcpt(c, v_adr);
end loop;
-- 解析 暗送人列表
v_tmp := i_bcc;
while (v_tmp is not null) loop
v_adr := getAddress(v_tmp, v_bcc);
utl_smtp.rcpt(c, v_adr);
end loop; utl_smtp.open_data(c);
utl_smtp.write_data(c, 'From: '||v_from||utl_tcp.CRLF);
utl_smtp.write_data(c, 'To: '||v_to||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Cc: '||v_cc||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Subject: '||EncodeAddress(i_subject)||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Mime-Version: 1.0'||utl_tcp.CRLF);
utl_smtp.write_data(c, 'Content-Type: text/plain; charset="GB2312"'||utl_tcp.CRLF);
utl_smtp.write_data(c, utl_tcp.CRLF); -- data
utl_smtp.write_raw_data(c, utl_raw.cast_to_raw(i_content));
utl_smtp.write_data(c, utl_tcp.CRLF); utl_smtp.close_data(c);
utl_smtp.quit(c); EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(c);
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END send_mail;
PROCEDURE init_map ;
FUNCTION encode(r IN RAW) RETURN VARCHAR2; END;
CREATE OR REPLACE PACKAGE BODY twd_base64 IS
TYPE map_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
map map_table;
-- Initialize the Base64 mapping
PROCEDURE init_map IS
BEGIN
map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
END;
FUNCTION encode(r IN RAW) RETURN VARCHAR2 IS
i pls_integer;
x pls_integer;
y pls_integer;
v VARCHAR2(32767);
BEGIN
-- For every 3 bytes, split them into 4 6-bit units and map them to
-- the Base64 characters
i := 1;
WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
to_number(utl_raw.substr(r, i + 2, 1), '0X');
y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
y := floor(x / 4096); v := v || map(y); x := x - y * 4096;
y := floor(x / 64); v := v || map(y); x := x - y * 64;
v := v || map(x);
i := i + 3;
END LOOP; -- Process the remaining bytes that has fewer than 3 bytes.
IF ( utl_raw.length(r) - i = 0) THEN
x := to_number(utl_raw.substr(r, i, 1), '0X');
y := floor(x / 4); v := v || map(y); x := x - y * 4;
x := x * 16; v := v || map(x);
v := v || '==';
ELSIF ( utl_raw.length(r) - i = 1) THEN
x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
to_number(utl_raw.substr(r, i + 1, 1), '0X');
y := floor(x / 1024); v := v || map(y); x := x - y * 1024;
y := floor(x / 16); v := v || map(y); x := x - y * 16;
x := x * 4; v := v || map(x);
v := v || '=';
END IF;
RETURN v;
END; BEGIN
init_map;
END;