快捷搜索:

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

近来将一些法度榜样写成存储历程履行,不停都想经由过程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

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, ''); --这里的'');--这里的'

4.测试

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

您可能还会对下面的文章感兴趣: