有没有人配置过高级的电子邮件通知方式

Question

CREATE OR REPLACE PROCEDURE send_mail (
  p_recipient IN   VARCHAR2,
  p_sender    IN   VARCHAR2,
  p_pass      IN   VARCHAR2,
  p_message   IN   VARCHAR2,
  p_subject   IN   VARCHAR2
)
IS
  v_mailhost varchar2(30);  -- SMTP服务器地址
  mail_conn utl_smtp.connection;
  msg varchar2(4000);
  p_user  varchar2(30);    -- 登录SMTP服务器的用户名
BEGIN
  select substr(p_sender,instr(p_sender,'@'),100) INTO p_user from dual;
  select substr(p_sender,1,instr(p_sender,'@')-1 ) INTO p_user from dual;  /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
  msg := 'Date:' ||to_char(sysdate,'dd mon yy hh24:mi:ss') || utl_tcp.CRLF ||
         'From: ' || p_sender || '<'||p_sender||'>' ||utl_tcp.CRLF ||
         'To: '|| p_recipient || '<' || p_recipient || '>' ||utl_tcp.CRLF||
         'Subject: '||p_subject || utl_tcp.CRLF ||utl_tcp.CRLF||
         p_message;
  -- dbms_output.put_line(msg);
  mail_conn := utl_smtp.open_connection(v_mailhost,25);
  utl_smtp.helo(mail_conn,v_mailhost);
  /* smtp服务器登录校验 */
  utl_smtp.command(mail_conn, 'AUTH LOGIN'); 
  utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user)))); 
  utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_pass))));   utl_smtp.mail(mail_conn,p_sender);
  utl_smtp.rcpt(mail_conn,p_recipient);
  /* 发送数据 */
  utl_smtp.data(mail_conn, msg);
  utl_smtp.quit(mail_conn);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_call_stack);
END send_mail;

Answer 1

不好意思，写错了
CREATE OR REPLACE PROCEDURE send_mail (
  p_recipient IN   VARCHAR2,
  p_sender    IN   VARCHAR2,
  p_pass      IN   VARCHAR2,
  p_message   IN   VARCHAR2,
  p_subject   IN   VARCHAR2
)
IS
  v_mailhost varchar2(30);  -- SMTP服务器地址
  mail_conn utl_smtp.connection;
  msg varchar2(4000);
  p_user  varchar2(30);    -- 登录SMTP服务器的用户名
BEGIN
  select substr(p_sender,instr(p_sender,'@')+1,100) INTO v_mailhost from dual;
  select substr(p_sender,1,instr(p_sender,'@')-1 ) INTO p_user from dual;  /* 创建要发送的邮件内容 注意报头信息和邮件正文之间要空一行 */
  msg := 'Date:' ||to_char(sysdate,'dd mon yy hh24:mi:ss') || utl_tcp.CRLF ||
         'From: ' || p_sender || '<'||p_sender||'>' ||utl_tcp.CRLF ||
         'To: '|| p_recipient || '<' || p_recipient || '>' ||utl_tcp.CRLF||
         'Subject: '||p_subject || utl_tcp.CRLF ||utl_tcp.CRLF||
         p_message;
  -- dbms_output.put_line(msg);
  mail_conn := utl_smtp.open_connection(v_mailhost,25);
  utl_smtp.helo(mail_conn,v_mailhost);
  /* smtp服务器登录校验 */
  utl_smtp.command(mail_conn, 'AUTH LOGIN'); 
  utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user)))); 
  utl_smtp.command(mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_pass))));   utl_smtp.mail(mail_conn,p_sender);
  utl_smtp.rcpt(mail_conn,p_recipient);
  /* 发送数据 */
  utl_smtp.data(mail_conn, msg);
  utl_smtp.quit(mail_conn);EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_stack);
    dbms_output.put_line(dbms_utility.format_call_stack);
END send_mail;