Problem:
--
As Scott User
CREATE OR REPLACE PROCEDURE send_mail (p_to
IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host,
p_smtp_port);
UTL_SMTP.helo(l_mail_conn,
p_smtp_host);
UTL_SMTP.mail(l_mail_conn,
p_from);
UTL_SMTP.rcpt(l_mail_conn,
p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: '
|| TO_CHAR(SYSDATE,
'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: '
|| p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: '
|| p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: '
|| p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: '
|| p_from || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf ||
UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/
begin
send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject =>
'TEST SEND MAIL',p_message =>'test',p_smtp_host =>
'127.0.0.1');
end;
/
*
ERROR at line 1:
ORA-24247:
network access denied by access control list (ACL)
ORA-06512:
at "SYS.UTL_TCP", line 17
ORA-06512:
at "SYS.UTL_TCP", line 246
ORA-06512:
at "SYS.UTL_SMTP", line 127
ORA-06512:
at "SYS.UTL_SMTP", line 150
ORA-06512:
at "SCOTT.SEND_MAIL", line 10
ORA-06512:
at line 2
Solution:
This is because the SCOTT does not have the privilege
to access the mail/smtp server. So it must be added to the ACL.
--
AS sys User
SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;
no rows selected.
This is because the SCOTT does not have the privilege
to access the mail/smtp server. So it must be added to the ACL.
Create and assign ACL.
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl =>
'send_mail.xml',
description =>
'Purpose of the acl is to send mail',
principal =>
'SCOTT',
is_grant =>
TRUE,
privilege =>
'connect',
start_date =>
SYSTIMESTAMP,
end_date =>
NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl =>
'send_mail.xml',
host =>
'127.0.0.1',
lower_port =>
1,
upper_port =>
9999);
COMMIT;
END;
/
SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;
HOST
|
LOWER_PORT
|
UPPER_PORT
|
ACL
|
127.0.0.1
|
1
|
9999
|
/sys/acls/send_mail.xml
|
--
Now try Again
-- As scott User
begin
send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject =>
'TEST SEND MAIL',p_message =>'test',p_smtp_host =>
'127.0.0.1');
end;
/
PL/SQL procedure successfully
completed.