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
Nice Post!!
ReplyDeletePlease Look Here At - In today's data-driven world, the performance and reliability of databases are paramount. Database Monitoring Services play a crucial role in ensuring that databases operate efficiently, securely, and without interruptions. These services provide insights into database health, performance metrics, and potential issues, allowing businesses to make informed decisions and maintain optimal operations.