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
It was really helpful... Thanks.
ReplyDeleteHi,
ReplyDeleteI 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
where is 1st comments
ReplyDeleteIs works.!
ReplyDeleteI have faced issue while executing the UTL_MAIL.SEND following error occurred
ReplyDeleteError 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