When you run the script you'll see
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.
But that GRANT is *not* on UTL_MAIL but on one of the internal routines needed to support it. So by default, most users will not be able to see it, eg
--
-- 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
So you need to do a normal grant, ie
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
Now UTL_MAIL is working, but you need to allow network privileges to let UTL_MAIL go out through the server port, so something like:
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;
/
depending on your mail host and the port it is listening on. (25 is typical for mail)