Solved

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

Posted on 2009-05-12
6
747 Views
Last Modified: 2013-11-30
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.
0
Comment
Question by:neild123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24370868
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
 

Author Comment

by:neild123
ID: 24371385
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
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24371440
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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 48

Expert Comment

by:schwertner
ID: 24374586
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
 

Author Comment

by:neild123
ID: 24381824
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
 

Author Comment

by:neild123
ID: 24418018
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

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question