LINUX AS+Oracle ||从Oracle成功发送邮件:
示例如下
具体的测试环境:LINUX AS Oracle
SQL> select * from v$version;
BANNER
Oraclei Enterprise Edition Release Production
PL/SQL Release Production
CORE Production
TNS for Linux: Version Production
NLSRTL Version Production
SQL> select * from v$version;
Oracle Database g Enterprise Edition Release Prod
PL/SQL Release Production
CORE Production
TNS for Linux: Version Production
NLSRTL Version Production
MAIL服务器为WINWINMAIL
保证ORACLE服务器到MAIL服务器网络畅通端口打开
创建发送邮件的procedure如下:
CREATE OR REPLACE PROCEDURE SEND_MAIL
(as_sender in varchar 邮件发送者
as_recp in varchar 邮件接收者
as_subject in varchar 邮件标题
as_msg_body in varchar) 邮件内容
IS
ls_mailhost varchar() := mail server; address or IP
lc_mail_conn nnection;
ls_subject varchar();
ls_msg_body varchar();
ls_username varchar() := usercode;
ls_password varchar() := password;
BEGIN
lc_mail_conn := utl_smtpopen_connection(ls_mailhost );
utl_smtphelo(lc_mail_conn ls_mailhost);
mand(lc_mail_conn AUTH LOGIN);
mand(lc_mail_conn demo_baseencode(utl_rawcast_to_raw(ls_username)));
mand(lc_mail_conn demo_baseencode(utl_rawcast_to_raw(ls_password)));
ls_subject := Subject: [ || upper(sys_context(userenv db_name)) || ] || as_subject;
ls_msg_body := as_msg_body;
utl_smtpmail(lc_mail_conn <||as_sender||>); 这里的< 一定要写不然会出现permanent error
utl_smtprcpt(lc_mail_conn <||as_recp||>);这里的< 一定要写不然会出现permanent error
utl_smtpopen_data(lc_mail_conn);
ls_msg_body := From: || as_sender || chr() || chr() || To: || as_recp || chr() || chr() || ls_subject ||
chr() || chr() || chr() || chr() || ls_msg_body;
utl_smtpwrite_raw_data(lc_mail_conn utl_rawcast_to_raw(ls_msg_body)); 这样写subject可以支持中文但body内容不支持中文;
utl_smtpwrite_data(lc_mail_conn ls_msg_body); 这样写subject不支持中文
utl_smtpclose_data(lc_mail_conn);
utl_smtpquit(lc_mail_conn);
EXCEPTION
WHEN UTL_SMTPINVALID_OPERATION THEN
dbms_outputput_line(invalid operation);
WHEN UTL_SMTPTRANSIENT_ERROR THEN
dbms_outputput_line(transient error);
WHEN UTL_SMTPPERMANENT_ERROR THEN
dbms_outputput_line(permanent error);
WHEN OTHERS THEN
dbms_outputput_line(others);
end send_mail;
执行发送邮件:
exec send_mail(我我this is a oracle test mail);
注意事项:上面的过程如果在编译中出现demo_baseencode must be declared请大家创建下面的包和包体;
CREATE OR REPLACE PACKAGE demo_base IS
Baseencode a piece of binary data
Note that this encode function does not split the encoded text into
multiple lines with no more than bytes each as required by
the MIME standard
FUNCTION encode(r IN RAW) RETURN VARCHAR;
END;
CREATE OR REPLACE PACKAGE BODY demo_base IS
TYPE vc_table IS TABLE OF VARCHAR() INDEX BY BINARY_INTEGER;
map vc_table;
Initialize the Base mapping
PROCEDURE init_map IS
BEGIN
map() :=A; map() :=B; map() :=C; map() :=D; map() :=E;
map() :=F; map() :=G; map() :=H; map() :=I; map():=J;
map():=K; map():=L; map():=M; map():=N; map():=O;
map():=P; map():=Q; map():=R; map():=S; map():=T;
map():=U; map():=V; map():=W; map():=X; map():=Y;
map():=Z; map():=a; map():=b; map():=c; map():=d;
map():=e; map():=f; map():=g; map():=h; map():=i;
map():=j; map():=k; map():=l; map():=m; map():=n;
map():=o; map():=p; map():=q; map():=r; map():=s;
map():=t; map():=u; map():=v; map():=w; map():=x;
map():=y; map():=z; map():=; map():=; map():=;
map():=; map():=; map():=; map():=; map():=;
map():=; map():=; map():=+; map():=/;
END;
FUNCTION encode(r IN RAW) RETURN VARCHAR IS
i pls_integer;
x pls_integer;
y pls_integer;
v VARCHAR();
BEGIN
For every bytes split them into bit units and map them to
the Base characters
i := ;
WHILE ( i + <= utl_rawlength(r) ) LOOP
x := to_number(utl_rawsubstr(r i ) X) * +
to_number(utl_rawsubstr(r i + ) X) * +
to_number(utl_rawsubstr(r i + ) X);
y := floor(x / ); v := v || map(y); x := x y * ;
y := floor(x / ); v := v || map(y); x := x y * ;
y := floor(x / ); v := v || map(y); x := x y * ;
v := v || map(x);
i := i + ;
END LOOP;
Process the remaining bytes that has fewer than bytes
IF ( utl_rawlength(r) i = ) THEN
x := to_number(utl_rawsubstr(r i ) X);
y := floor(x / ); v := v || map(y); x := x y * ;
x := x * ; v := v || map(x);
v := v || ==;
ELSIF ( utl_rawlength(r) i = ) THEN
x := to_number(utl_rawsubstr(r i ) X) * +
to_number(utl_rawsubstr(r i + ) X);
y := floor(x / ); v := v || map(y); x := x y * ;
y := floor(x / ); v := v || map(y); x := x y * ;
x := x * ; v := v || map(x);
v := v || =;
END IF;
RETURN v;
END;
BEGIN
init_map;
END;
结束
