Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, VINOD.

Asked: September 16, 2017 - 9:31 am UTC

Last updated: November 27, 2018 - 4:23 am UTC

Version: oracle 11g

Viewed 50K+ times! This question is

You Asked

Dear Team,

I tried to install UTL_MAIL using the below command

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

it runs successfully and created the package and synonym but when i tried to grant to UTL_MAIL TO any user, its shows the error message "ora-00942 table or view not exist "

and one question how i configure the SMTP Server, My database is oracle 11g on Unix Operating System and my application is on Windows Operating system. so my question is which operating system I would make the SMTP server. so what should I write to execute the below statement on smtp_out_server.

ALTER SYSTEM SET smtp_out_server = '10.206.2.21:25' scope=both;

and also when i run the below block in toad it thorws error that "PLS-00201: identifier 'UTL_MAIL' must be declared"

BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
UTL_MAIL.send(sender => 'me@address.com',
recipients => 'you@address.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;

Thanks

and Connor said...

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)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 18, 2017 - 12:36 pm UTC

Thank you so much cornor for your answer . I will try what you have suggested. I think it will work.

again Thank you
Connor McDonald
September 19, 2017 - 2:05 am UTC

glad we could help

Still not working. Are there logs for further troubleshooting?

Jonathon, November 27, 2018 - 12:39 am UTC

Thank you for these instructions. They are the best available for setting up UTL_MAIL in Oracle.

We are running Oracle 12c. I have executed all your instructions above, and the code seems to run successfully, yet I am not seeing anything in our local mailserver's queues, and not receiving any messages sent from UTL_MAIL. Are there some log tables or files with information about each message sent and whether or not it was successful?

I have checked trash and spam folders for messages sent from UTL_MAIL. I am not getting any exceptions from the UTL_MAIL.send code that I am running, and I have copied your code above for it. TIA.

Please delete previous review

Jonathon, November 27, 2018 - 12:42 am UTC

Sorry. After posting my review a few minutes ago, I thought to check the recipients, and it was going to a distribution list which apparently was the problem. You may delete my review. The problem is apparent to me now.

Best regards.
Connor McDonald
November 27, 2018 - 4:23 am UTC

Thanks for letting us know.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library