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;
結束
From:http://tw.wingwit.com/Article/program/Oracle/201311/18084.html
