利用oracle存储过程发送邮件

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';
本文来自Sqlclub


  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);
本文来自Sqlclub

  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;

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

 

posted @ 2010-07-17 00:23  你妹的sb  阅读(365)  评论(0编辑  收藏  举报
百度一下