熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

實例講解如何通過Oracle成功發送郵件

2013-11-13 16:17:03  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.