Thursday, January 7, 2016

How to send Mail through database using UTL_MAIL??


As a sys user execute below scripts
{ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb
SMTP_OUT_SERVER parameter must be set to identify the SMTP server (We can give SMTP FQDN or IP address)
SQL> alter system set smtp_out_server='smtp.server.com:25' scope=both;
SQL> grant execute on UTL_MAIL to public;
Create ACL (Access Control list)
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'PUBLIC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;

/
Assign the ACL to the SMTP (Mail server)
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'smtp.server.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;

/
Example send procedure
SQL>declare
begin
 utl_mail.send(
   sender => 'abc@xyz.com',
   recipients => 'xyz@abc.com',
   subject => 'Testing utl_mail',
   message => 'The receipt of this email means'||
              'UTL_MAIL works for you!'
   );
EXCEPTION
   WHEN OTHERS THEN
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);   
END;

/

PL/SQL procedure successfully completed.

Check you inbox you should see Test mail!!

No comments: