I can I tell what utl_mail did with my out going messages?

Hi Gurus,

I am using the Oracle 11g pl/sql package - utl_mail.send to send emails to users that have logged in to my website. But some of these mails are not getting through. I get no errors (that I can find), they just don't turn up. I know very little about SMTP stuff. Could someone please let me know where to start looking.

Thank you.
neild123Asked:
Who is Participating?
 
mrjoltcolaConnect With a Mentor Commented:
1) You can use another agent / program to send the exact mail via the exact sender / recipient, then see if the behavior is the same.

2) Ask your mail ISP, or your mail administrator to confirm or review their log. Could be a server based spam filter.
0
 
mrjoltcolaCommented:
Are you handling all exceptions and logging the exceptions?

Something like below?

    EXCEPTION
      WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
    BEGIN
      INSERT INTO MAIL_LOG(email, 'Failed to send mail due to the following error: ' || sqlerrm);
    END;

Open in new window

0
 
neild123Author Commented:
Yes, but it is not throwing any errors. Some emails simply don't arrive. I don't think the issue is with utl_mail. I think it is further on. I just don't know how to prove it.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
schwertnerCommented:
You have to see the error log of the SMTP server.
Your sender has no address so you will not get error message there.
But the mail server has to log the incomming and the sent messages.

See also the sample code.
PROCEDURE SEND_MAIL
(V_MAILTO IN VARCHAR2
,V_MAILFROM IN VARCHAR2
,V_SUBJECT IN VARCHAR2
,V_BODY IN LONG
,V_CC IN VARCHAR2 := null
,V_BCC IN VARCHAR2 := null
)
IS
-- PL/SQL Specification
CONN UTL_SMTP.CONNECTION;
dato varchar2(4000);
 
-- PL/SQL Block
BEGIN
DECLARE
CONN UTL_SMTP.CONNECTION;
dato VARCHAR2(4000);
nSqlCode NUMBER(8);
vSqlErrm VARCHAR2(4000);
vSrv VARCHAR2(100);
 
BEGIN
 
vSrv := 'smtp.server.com;
CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);
UTL_SMTP.HELO(CONN,'Origin.server.com');
UTL_SMTP.MAIL(CONN,v_mailfrom);
UTL_SMTP.RCPT(CONN,v_mailto);
UTL_SMTP.OPEN_DATA(CONN);
 
UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);
UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);
 
-- Splits body (long) into varchar2s.
 
FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP
 
DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);
 
END LOOP;
 
UTL_SMTP.CLOSE_DATA(CONN);
UTL_SMTP.QUIT(CONN);
 
 
EXCEPTION
WHEN utl_smtp.transient_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
 
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;
 
BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;
 
raise_application_error(-20100, vSqlErrm);
 
WHEN utl_smtp.permanent_error THEN
nSqlCode := SQLCODE;
vSqlErrm := SQLERRM;
BEGIN
UTL_SMTP.CLOSE_DATA(CONN);
EXCEPTION WHEN OTHERS THEN NULL;
END;
 
BEGIN
utl_smtp.quit(conn);
EXCEPTION WHEN OTHERS THEN NULL;
END;
 
raise_application_error(-20200, vSqlErrm);
END;
END SEND_MAIL;

Open in new window

0
 
neild123Author Commented:
I think mrjoltcola may be on the right track.

If I send the emails with the sender set to my email address then they arrive.
If I send then from registrations@chesstrix.com they don't.

This email address doesn't exist and I don't want it to.
I'll check with my ISP and get back to you.
0
 
neild123Author Commented:
OK, I worked it out. You cannot send emails from a non verifiable address. They get blocked by a SPAM filter somewhere outside my ISP. I made a verifiable address, sent an email to it to test things out and now utl_mail can send messages to my users using this email address as the sender.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.