ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用oracle存储过程来发送邮件

利用oracle存储过程来发送邮件

原创 Linux操作系统 作者:阿日 时间:2008-08-28 14:18:14 0 删除 编辑
最近将一些程序写成存储过程执行,一直都想通过Email方式通知我,每次执行的结果是否正确,在网上找了一下通过oracle发送Email,一找就找到了,调试一下便可以用了,现在记录一下,以便以后的学习使用!
目前我计划将一些统计的报表及数据库的信息等,每天能够定时的发到我的邮箱里,这样使每天的例行工作简单化,高效化,虽然是从网上"抄"来的,但会认真的研读一下代码,提高一下! 
1.先创建demo_base64包,这里包括一个函数encode主要是用来解码
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;
2.创建demo_base64包体
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;

3.创建SendMail过程这里主要高用utl_smtp这个包
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) := '***.***.***.***'; -- address or IP
lc_mail_conn utl_smtp.connection;
ls_subject varchar2(100);
ls_msg_body varchar2(20000);
ls_username varchar2(256) := 'zhangxf';
ls_password varchar2(256) := 'libozxf';
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;

4.测试
exec send_mail('zxf_feng@f163.com','zxf_feng@163.com','test','test');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67668/viewspace-438661/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-26

  • 博文量
    35
  • 访问量
    144460