|
【赛迪网－it技术报道】linux as3+oracle 9.2||10.20.1从oracle成功发送邮件:
示例如下：
具体的测试环境:linux as3 , oracle 9.0.2.4
sql> select * from v$version;
banner
----------------------------------------------------------------
oracle9i enterprise edition release 9.2.0.4.0 - production
pl/sql release 9.2.0.4.0 - production
core 9.2.0.3.0 production
tns for linux: version 9.2.0.4.0 - production
nlsrtl version 9.2.0.4.0 - production
sql> select * from v$version;
oracle database 10g enterprise edition release 10.2.0.1.0 - prod
pl/sql release 10.2.0.1.0 - production
core 10.2.0.1.0 production
tns for linux: version 10.2.0.1.0 - production
nlsrtl version 10.2.0.1.0 - production
mail服务器为win2003,winmail
1.保证oracle服务器到mail服务器网络畅通,25端口打开
2.创建发送邮件的procedure如下:
---------------------------------------
create or replace procedure send_mail
(as_sender in varchar2, --邮件发送者
as_recp in varchar2, --邮件接收者
as_subject in varchar2, --邮件标题
as_msg_body in varchar2) --邮件内容
is
ls_mailhost varchar2(30) := 'mail server'; -- address or ip
lc_mail_conn utl_smtp.connection;
ls_subject varchar2(100);
ls_msg_body varchar2(20000);
ls_username varchar2(256) := 'usercode';
ls_password varchar2(256) := 'password';
begin
lc_mail_conn := utl_smtp.open_connection(ls_mailhost, 25);
utl_smtp.helo(lc_mail_conn, ls_mailhost);
utl_smtp.command(lc_mail_conn, 'auth login');
utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_username)));
utl_smtp.command(lc_mail_conn, demo_base64.encode(utl_raw.cast_to_raw(ls_password)));
ls_subject := 'subject: [' || upper(sys_context('userenv', 'db_name')) || '] - ' || as_subject;
ls_msg_body := as_msg_body;
utl_smtp.mail(lc_mail_conn, '<'||as_sender||'>'); --这里的'<' 一定要写,不然会出现permanent error
utl_smtp.rcpt(lc_mail_conn, '<'||as_recp||'>');--这里的'<' 一定要写,不然会出现permanent error
utl_smtp.open_data(lc_mail_conn);
ls_msg_body := 'from: ' || as_sender || chr(13) || chr(10) || 'to: ' || as_recp || chr(13) || chr(10) || ls_subject ||
chr(13) || chr(10) || chr(13) || chr(10) || ls_msg_body;
utl_smtp.write_raw_data(lc_mail_conn, utl_raw.cast_to_raw(ls_msg_body)); --这样写subject可以支持中文但body内容不支持中文;
-- utl_smtp.write_data(lc_mail_conn, ls_msg_body); --这样写subject不支持中文
utl_smtp.close_data(lc_mail_conn);
utl_smtp.quit(lc_mail_conn);
exception
when utl_smtp.invalid_operation then
dbms_output.put_line('invalid operation');
when utl_smtp.transient_error then
dbms_output.put_line('transient error');
when utl_smtp.permanent_error then
dbms_output.put_line('permanent error');
when others then
dbms_output.put_line('others');
end send_mail;
3.执行发送邮件:
exec send_mail('heyu@163.net','admin@163.net','我我','this is a oracle test mail');
注意事项:上面的过程如果在编译中出现demo_base64.encode must be declared,请大家创建下面的包和包体;
---------------------------------
create or replace package demo_base64 is
-- base64-encode a piece of binary data.
--
-- note that this encode function does not split the encoded text into
-- multiple lines with no more than 76 bytes each as required by
-- the mime standard.
--
function encode(r in raw) return varchar2;
end;
------------------------------------
create or replace package body demo_base64 is
type vc2_table is table of varchar2(1) index by binary_integer;
map vc2_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;
--结束.
|
