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:
Post a Comment