APEX 发送邮件

来源：互联网发布：淘宝男装潮流店铺编辑：程序博客网时间：2024/05/22 16:58

APEX在Oracle数据库UTL_SMTP包基础上提供了自己的发送邮件的API APEX_MAIL.使用方法如下：

APEX_MAIL.SEND(    p_to                        IN    VARCHAR2,    p_from                      IN    VARCHAR2,    p_body                      IN  [ VARCHAR2 | CLOB ],    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT,    p_subj                      IN    VARCHAR2 DEFAULT)    p_cc                        IN    VARCHAR2 DEFAULT)    p_bcc                       IN    VARCHAR2 DEFAULT);    p_replyto                   IN    VARCHAR2 DEFAULT);RETURN NUMBER;

一个简单的例子：

-- Example One: Plain Text only messageDECLARE    l_body      CLOB;BEGIN    l_body := 'Thank you for your interest in the APEX_MAIL package.'||utl_tcp.crlf||utl_tcp.crlf;    l_body := l_body ||'  Sincerely,'||utl_tcp.crlf;    l_body := l_body ||'  The APEX Dev Team'||utl_tcp.crlf;    apex_mail.send(        p_to       => 'some_user@somewhere.com',   -- change to your email address        p_from     => 'some_sender@somewhere.com', -- change to a real senders email address        p_body     => l_body,        p_subj     => 'APEX_MAIL Package - Plain Text message');END;/

对于Oracle 11gR2来说，可能邮件发送失败（ORA-24247: network access denied by access control list (ACL)）或者邮件没有发送出去。11g采用了更严格的网络服务安全控制ACLs(Access Control Lists)，可以用sysdba用户登陆，修改设计如下：

begindbms_network_acl_admin.create_acl (   acl          => 'networkacl.xml',   description  => 'Allow Network Connectivity',   principal    => 'PUBLIC',   is_grant     => TRUE,   privilege    => 'connect',   start_date   => SYSTIMESTAMP,   end_date     => NULL);dbms_network_acl_admin.assign_acl (   acl         => 'networkacl.xml',   host        => '*',   lower_port  => NULL,   upper_port  => NULL);commit;end;

上面的principal是PUBLIC，可能不安全，可以根据需要修改为特定的schma name，比如SCOTT

另外还要设置SMTP host

1. 登陆apex_admin

2. Manage Instance -> Instance Setting ->Email

3. Set the SMTP HOST ADDR

参考：

【1】http://docs.oracle.com/cd/E10513_01/doc/apirefs.310/e12855/apex_mail.htm#CIHDIEJI

【2】http://diznix.com/2010/12/06/oracle-11g-network-access-control-lists/

【3】http://oraexplorer.com/2009/10/the-access-control-lists-to-network-services-e-g-utl_http-utl_smtp-utl_tcp-etc-in-oracle-11g/