Saturday, November 27, 2010

How to Configure UTL_MAIL in Oracle DB

To install UTL_MAIL:

sqlplus / as SYSDBA

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

When you run the above mentioned two scripts, Oracle creates UTL_MAIL package. Oracle also creates a public synonym on UTL_MAIL package, but doesn't grant privileges to any user, DBA role or PUBLIC. You need to explicitly grant EXECUTE privilege on this package.

SQL> GRANT EXECUTE ON UTL_MAIL TO APPOWNER, JMEHTA;

Grant succeeded.

In addition, define int.ora parameter as shown below.

SQL> alter system set smtp_out_server= 'mysmtpserver.mydomain.com' scope both;

If you are in Oracle11g, configure network access control lists.

That's all you need to setup UTL_MAIL package. Send a test  email

exec UTL_MAIL.SEND (sender=>'me@mydomain.com', recipients=>'you@google.com', subject=>'Test Message', Message=>'test'); 

If you are receiving the following error, then double-check that SMTP server that you have defined is accessible.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671




5 comments:

  1. It was really helpful... Thanks.

    ReplyDelete
  2. Hi,

    I followed the steps given above.I can able to describe the UTL_Mail but not able to execute it. I am getting below error
    "PL/SQL: could not find program unit being called: "BHASKAR.UTL_MAIL"".

    Please help me to resolve this error

    ReplyDelete
  3. I have faced issue while executing the UTL_MAIL.SEND following error occurred
    Error at line 1
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "SYS.UTL_MAIL", line 654
    ORA-06512: at "SYS.UTL_MAIL", line 671
    ORA-06512: at line 1

    ReplyDelete