Oracle 11g 使用 UTL_MAIL 包实现Procedure发送邮件

想要通过Oracle的Procedure发送邮件，参考了这篇文章， 但是到了给非 SYS 用户赋权的时候就出错了。

具体出错是在以下这个命令时：

grant execute on dbms_network_acl_admin to al1

--上述命令中的al1是作者举例的，请替换成自己的Oracle用户。

原因是dbms_network_acl_admin这个对象根本不存在。

遇到的错误提示是：ORA-24248: XMLDB extensible security not installed，通过以下SQL语句也能确认。

SELECT comp_name , status FROM dba_registry;

结果中没有Oracle XML Database这一项。

所以我们通过下述步骤来安装它。进入$ORACLE_HOME/rdbms/admin目录，确认catqm.sql这个文件存在，用SYS用户以SYSDBA身份登入sqlplus，分别执行以下语句：

spool install_xml_db.log --这个log文件名随意
@catqm xdb sysaux temp YES --安装XML Database，过程大概十分钟，等待完成。

继续以下指令：

DECLARE
    suf  varchar2(26);
    stmt varchar2(2000);
  BEGIN
    select toksuf into suf from xdb.xdb$ttset where flags = 0;
    stmt := 'grant all on XDB.X$PT' || suf || ' to DBA';
    execute immediate stmt;
    stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION';
    execute immediate stmt;
  END;
  /

等待显示“PL/SQL procedure successfully completed.”后执行spool off。 

现在再去查comp_name应该就有XML Database了。SQL语句：

SELECT comp_name , status FROM dba_registry; 

只安装了XML Database还不够，还会出现ORA-24247的错误，继续进行ACL授权。

继续用SYS用户以SYSDBA身份登入sqlplus，执行以下语句（注意替换principal为将来用于发邮件的Oracle用户）：

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'mail_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
    );
END;
/

完成后提示“PL/SQL procedure successfully completed.”。

我在执行上述procedure时忘了改SCOTT，但因为SCOTT是默认用户，所以也成功了，要添加自己想要的用户，再执行下述语句：

BEGIN 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
       acl     => 'mail_acl.xml',
       principal => '你想要的用户',
       is_grant  => true,
       privilege => 'resolve'
    );
END;
/

更多信息请参考Oracle官方文档：https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#BABEHFAG

言归正传，接着执行如下语句进行ACL授权：

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         =>'mail_acl.xml',
     host        => 'localhost',
     lower_port  => null,
     upper_port  => null
     );
END;
/

正确的话提示跟上面相同。这段语句里的内容不要改。 

上面内容完成后还要记得执行commit。

好了，现在ACL授权的问题解决了。用要发邮件的Oracle用户登录sqlplus，执行如下语句进行验证：

column global_name format a20
column get_host_address format a15
select global_name,utl_inaddr.get_host_address from global_name;

如果返回值是Oracle服务器的名称和IP地址，就没问题了。

现在可以执行本文开头的语句，进行授权了。（ps：用SYS权限）

顺利的话，接着进行设定权限项目和指定邮件服务器地址和端口。

同样使用SYS用户以SYSDBA身份执行（同样要替换principal的值）：

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
      acl => 'mail_server.xml',
      description => 'mail server',
      principal => 'AL1',
      is_grant => TRUE,
      privilege => 'connect');
END;
/

和

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
       acl => 'mail_server.xml',
       host => '192.168.0.100',
       lower_port => 25);
END;
/

好了，可以用普通用户发邮件测试一下了。

begin
  utl_mail.send(sender => 'oracle@maxwa.xyz',
                recipients => 'max@maxwa.xyz',
                message => 'Test only.',
                subject => 'Test from Oracle');
end;
/

如果结果是“PL/SQL procedure successfully completed”，那么恭喜你，大功告成！