Not able to receive email from Oracle

user12251389

I have created below Procedure for sending an email.

create or replace PROCEDURE send_mail_smtp (   sender     IN VARCHAR2, recipient  IN VARCHAR2, subject    IN VARCHAR2, message    IN LONG )IS   mailhost     VARCHAR2(30) := 'w010a893.k.com';  -- -- host mail address   mail_conn    utl_smtp.connection ;   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );   mesg long;BEGIN   mail_conn := utl_smtp.open_connection(mailhost, 25);   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||          'From:  <'||sender||'>' || crlf ||          'Subject: '||subject || crlf ||          'To: '||recipient || crlf ||          '' || crlf || message;   utl_smtp.helo(mail_conn, mailhost);   utl_smtp.mail(mail_conn, sender);   utl_smtp.rcpt(mail_conn, recipient);   utl_smtp.data(mail_conn, mesg);   utl_smtp.quit(mail_conn);END;

And when i am executing the procedure i am not getting any error but i am not receiving any email.

BEGINSEND_MAIL_SMTP('[email protected]',           --Sender'[email protected]',           --Recipient'Test Mail',                    --Subject'Send From Oracle10g Database'  --Message);END;

I have run the below alter command :

ALTER SYSTEM SET smtp_out_server='w010a893.k' SCOPE=both;

Also i have creatd ACL. When i ran the below sql statement i am getting records as:

SELECT * FROM dba_network_acls;

SELECT * FROM dba_network_acl_privileges where principal='RATOR_MONITORING';

I dont know for what reason i am not receiving an email: Below is the detail for my SMTP :

BluShadow

Does your SMTP server show details of the message being received by it? (i.e. the communication from the database to the SMTP server)

Also, for the record, please note that:

utl_smtp.helo(mail_conn, mailhost); 

should not be the mailhost, it should be the server that is passed.  The SMTP server already knows itself; the purpose of the HELO command is to tell the SMTP server who is communicating with it i.e. the database server.  (and you can be forgiven as Oracle has got it wrong too in the documentation)

user12251389

How do i check whether my SMTP server show details of the message being received by it ? And if the utl_smtp.helo then what will be the correct mailhost i should provide ?

BluShadow

Checking your SMTP server depends on what email server your company is using (speak to your email administrators).

For the HELO command you're not providing a "correct mailhost", your providing the name of the server.  Consider the command as your program talking to the SMTP server and saying "Hello, I am <oracle server name>".  The way you currently have it, you're saying "Hello, I am w010a893.k.com", but that's the name of your mail host (SMTP server)... which is wrong, as your code is not the mail server, it's the oracle server.

As I said, Oracle documentation (and a lot of websites who have just blindly copied the documentation) have got it wrong.

user12251389

I have checked the smtp server now and also i have corrected the mailhost from utl_smtp.helo() to the <oracle server name> but still i ma receiving an email. Can you please tell me is there any other parameter i have to change ? And also do i set the ACL properly ?

BluShadow

I have checked the smtp server now

And the results of that check are?  Did the server have any communication from your code?

If it was an ACL issue then you should be getting an exception telling you that the ACL is blocking it.

However, I notice in your code that your ACL is defined for 'w010a893.k' as is your SMTP_OUT_SERVER parameter, and your mail host in your code is 'w010a893.k.com'

user12251389

Yes  ACL is defined for 'w010a893.k' as SMTP_OUT_SERVER parameter and also the mail host is SMTP_OUT_SERVER. When i do

show parameter smtp_out_server

the result i am getting i am setting it for ACL and for mail host which i have mentioned in my Procedure

BluShadow

My point was that your code has ".com" on the end of the mail host name which isn't the same