Oracle 使用utl_mail发送邮件

亲爱的团队，

我尝试使用以下命令安装UTL_MAIL

sqlplus系统/
SQL> @ $ ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @ $ ORACLE_HOME/rdbms/admin/prvtmail.plb

它成功运行并创建了软件包和同义词，但是当我尝试向任何用户授予UTL_MAIL时，它显示错误消息 “ora-00942表或视图不存在”

还有一个问题，我如何配置SMTP服务器，我的数据库是Unix操作系统上的oracle 11g，而我的应用程序是Windows操作系统上的。所以我的问题是我将制作SMTP服务器的哪个操作系统。所以我应该写什么来在smtp_out_server上执行下面的语句。

更改系统集smtp_out_server = '10.206.2.21:25' 范围 = 两者;

而且，当我在toad中运行以下块时，它还会错误 “必须声明PLS-00201: 标识符 'utl_mail '”

开始
立即执行 '更改会话集smtp_out_server =' 127.0.0.1 ';
邮件发送 (发件人 => “me@address.com”，
收件人 => “you@address.com”，
主题 => '测试邮件'，
消息 => '你好世界'，
mime_type => '文本; charset = us-ascii');
结束;

谢谢

当你运行脚本时，你会看到

SQL> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> conn / as sysdba
Connected.

SQL> @?/rdbms/admin/utlmail.sql

Package created.


Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb

Package created.


Package body created.


Grant succeeded.


Package body created.

但是，该授权不是在UTL_MAIL上，而是在支持它所需的内部例程之一上。因此，默认情况下，大多数用户将无法看到它，例如

--
-- SYS
--
SQL> BEGIN
  2  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  3  UTL_MAIL.send(sender => 'me@address.com',
  4  recipients => 'you@address.com',
  5  subject => 'Test Mail',
  6  message => 'Hello World',
  7  mime_type => 'text; charset=us-ascii');
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available  -- fine, just means I dont have a mail server running
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 3


SQL> conn scott/tiger
Connected.

SQL> BEGIN
  2  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  3  UTL_MAIL.send(sender => 'me@address.com',
  4  recipients => 'you@address.com',
  5  subject => 'Test Mail',
  6  message => 'Hello World',
  7  mime_type => 'text; charset=us-ascii');
  8  END;
  9  /
UTL_MAIL.send(sender => 'me@address.com',
*
ERROR at line 3:
ORA-06550: line 3, column 1:
PLS-00201: identifier 'UTL_MAIL' must be declared
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored

所以你需要做一个正常的资助，即

SQL> conn / as sysdba
Connected.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL>
SQL> BEGIN
  2  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  3  sys.UTL_MAIL.send(sender => 'me@address.com',
  4  recipients => 'you@address.com',
  5  subject => 'Test Mail',
  6  message => 'Hello World',
  7  mime_type => 'text; charset=us-ascii');
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 3

现在UTL_MAIL正在工作，但是您需要允许网络特权让UTL_MAIL通过服务器端口出去，所以类似:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'scott_email.xml', 
    description  => 'Email for SCOTT',
    principal    => 'SCOTT',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'scott_email.xml',
    host => 'my.mailhost.com', 
    lower_port => 25,
    upper_port => 25); 
  COMMIT;
END;
/

取决于您的邮件主机及其正在侦听的端口。(25是典型的邮件)