Send Emails from PL/SQL .

1.要想从DB Server发邮件，首先得确保DB Server已经 启用了SMTP(Simple Mail Transfer Protocol)服务。检查是否启动SMTP的方法
telnet server.domain.com 25
(25是SMTP的默认端口)

2.PL/SQL to send email sample code:

[sql] view plain copy print ?

1. DECLARE  
2.    lc_from_email_id   VARCHAR2 (100);  
3.    lc_mailhost        VARCHAR2 (100)      := 'abc.com';  
4.    lc_connection      UTL_SMTP.connection;  
5.    v_mail_sub         VARCHAR2 (200)      := 'Hello';  
6.    v_mail_body        VARCHAR2 (1000)     := 'This is the mail body';  
7.    v_from_email       VARCHAR2 (100)      := 'ptian@xxx.com';  
8.    v_to_email         VARCHAR2 (100)      := 'ptian@xxx.com';  
9. BEGIN  
10.    lc_connection := UTL_SMTP.open_connection (lc_mailhost, 25);  
11.    UTL_SMTP.helo (lc_connection, lc_mailhost);  
12.    UTL_SMTP.mail (lc_connection, v_from_email);  
13.    UTL_SMTP.rcpt (lc_connection, v_to_email);  
14.    UTL_SMTP.open_data (lc_connection);  
15.    UTL_SMTP.write_data (lc_connection, 'Subject' || ':' || v_mail_sub || ' ' || UTL_TCP.crlf);  
16.    UTL_SMTP.write_data (lc_connection, UTL_TCP.crlf || v_mail_body || UTL_TCP.crlf);  
17.    UTL_SMTP.close_data (lc_connection);  
18.    UTL_SMTP.quit (lc_connection);  
19. EXCEPTION  
20.    WHEN OTHERS  
21.    THEN  
22.       DBMS_OUTPUT.put_line ('Mail not sent');  
23. END;  

DECLARE
   lc_from_email_id   VARCHAR2 (100);
   lc_mailhost        VARCHAR2 (100)      := 'abc.com';
   lc_connection      UTL_SMTP.connection;
   v_mail_sub         VARCHAR2 (200)      := 'Hello';
   v_mail_body        VARCHAR2 (1000)     := 'This is the mail body';
   v_from_email       VARCHAR2 (100)      := 'ptian@xxx.com';
   v_to_email         VARCHAR2 (100)      := 'ptian@xxx.com';
BEGIN
   lc_connection := UTL_SMTP.open_connection (lc_mailhost, 25);
   UTL_SMTP.helo (lc_connection, lc_mailhost);
   UTL_SMTP.mail (lc_connection, v_from_email);
   UTL_SMTP.rcpt (lc_connection, v_to_email);
   UTL_SMTP.open_data (lc_connection);
   UTL_SMTP.write_data (lc_connection, 'Subject' || ':' || v_mail_sub || ' ' || UTL_TCP.crlf);
   UTL_SMTP.write_data (lc_connection, UTL_TCP.crlf || v_mail_body || UTL_TCP.crlf);
   UTL_SMTP.close_data (lc_connection);
   UTL_SMTP.quit (lc_connection);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Mail not sent');
END;

ps:可以通过下边的Query来获取SMTP Server Name

[sql] view plain copy print ?

1. select fscpv.parameter_value smtp_server_name  
2. from   fnd_svc_comp_params_tl fscpt  
3. ,      fnd_svc_comp_param_vals fscpv  
4. ,      fnd_svc_components fsc  
5. where  fscpt.parameter_id = fscpv.parameter_id  
6. and    fscpv.component_id = fsc.component_id  
7. and    fscpt.display_name = 'Outbound Server Name'  
8. and    fsc.component_name = 'Workflow Notification Mailer';