Skip to Main Content

SQL & PL/SQL

Not able to receive email from Oracle

user12251389Feb 21 2017 — edited Feb 21 2017

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.

BEGIN

SEND_MAIL_SMTP(

'support@leot.com',           --Sender

'r.des@leot.com',           --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;

pastedImage_33.png

SELECT * FROM dba_network_acl_privileges where principal='RATOR_MONITORING';

pastedImage_38.png

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

pastedImage_39.png

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 21 2017
Added on Feb 21 2017
7 comments
647 views