Email From Oracle PL/SQL (UTL_SMTP) not working

[jjcarranza]

Hi Tim,

I'm trying to work with UTL_SMTP in order to send emails from database server. I have followed your great articles regarding ACLs. These are the steps I have followed in order to make this work. As yo can see almost at the bottom of my message I'm able to make utl_mail.send works but when trying UTL_SMTP I'm receiving no errors but I'm not receiving an expected email either. Looking forward for your response. Thanks!


Oracle version: 11.2.0.2.0
OS: Windows Server 2003 Standard Edition


conn / as sysdba
alter system set smtp_out_server = '10.101.19.135' scope=spfile;
GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO USER;
GRANT EXECUTE ON UTL_TCP TO USER;
GRANT EXECUTE ON UTL_SMTP TO USER;
GRANT EXECUTE ON UTL_MAIL TO USER;
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO USER;
grant execute on SYS.utl_http to USER;
GRANT EXECUTE ON SYS.UTL_MAIL TO USER;

==============
= CREATE ACL =
==============

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Allow mail to be sent',
principal => 'USER',
is_grant => TRUE,
privilege => 'connect'
);
end;
/

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_mail.xml',
principal => 'USER',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;
/

begin
dbms_network_acl_admin.assign_acl (
acl => 'utl_mail.xml',
host => '10.101.19.135',
lower_port => 25,
upper_port => 25);
commit;
end;
/

conn user/user
-- THIS PROCEDURE WORKS
begin
utl_mail.send(
sender => 'jesus.carranza@celanese.com.mx',
recipients => 'jesus.carranza@celanese.com.mx',
message => 'Hello World'
);
end;
/

-- THIS IS NOT WORKING
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from 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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
/

BEGIN
send_mail(p_to => 'user@domain.com',
p_from => 'user@domain.com',
p_message => 'This is a test message.',
p_smtp_host => '10.101.19.135');
END;
/

PL/SQL procedure successfully completed.

But no email received...

[Tim...]

Hi.

The fact UTL_MAIL works and UTL_SMTP does not suggests this is nothing to do with the ACL.

Out of curiostity, what is your smtp_out_server parameter set to for the database?

Cheers

Tim...

[jjcarranza]

I have set:
alter system set smtp_out_server = '10.101.19.135' scope=spfile;
I have also tried a telnet connection using: telnet 10.101.19.135 25 and it's working.
Thanks

[Tim...]

Hi.

Do you have an exception handler in your send_mail procedure that is capturing (and hiding) error conditions? If it is failing, you should see an exception, unless your code is hiding it.

Cheers

Tim...

[jjcarranza]

I'm using the example you provide in your article 'Email From Oracle PL/SQL (UTL_SMTP)' which has no error handling portion:

CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from 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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;

Can you help me creating one?

[Tim...]

Hi.

My point was that the exception handler could be masking the errors. The fact you don't have one is a good thing in this case as any errors should be visible. The fact they are not is a worry.

In the example you gave, the UTL_MAIL was using a real email address, which the send_mail example was using a dummy email address. Is this really the email address you are using?

Assuming this is not the email address you are using, have you checked the real email mailbox is not blocking the mails as spam?

Cheers

Tim...

[jjcarranza]

In fact I'm using a real email address which is my own box I have checked and my mailbox is not blocking these emails. I have asked network people and they say we were using a domain (mailed.domain.com) instead of the IP (10.101.19.135) that's why PL/SQL developer was asked to change it in all stored procedures. I'm a DBA and this task was assigned to me since the developer is enjoying Xmas holiday. I was told that before this change everything was working properly. One workaround would be to use UTL_MAIL instead of utl_smtp but I'm guessing this is too risky since there are like 10 stored procedures and 30 triggers. Any clue on where to look for any kind of error?

[Tim...]

Hi.

1) I'm not clear from your post what you are trying to achieve. Which of these two is what is happening:

a) You were using the IP address, but now you want to use the fully qualified name.
b) You were using the fully qualified name, but now you want to use the IP address.

Please confirm.

2) You mentioned before that you did this:

Code: Select all

alter system set smtp_out_server = '10.101.19.135' scope=spfile;

This change would not take effect until the DB was restarted. Have you bounced the DB since you made that change?

Note. What I'm trying to get at is one method works and the other doesn't. This is strange, which makes me think you are not using the same values in each test.Please confirm both methods are using the same reference to the mail server (both IP address or both fully qualified name)

Also, please separate your points. It's hard to read your posts as separate points seem to merge in your paragraph, making me a little confused.

Cheers

Tim...