?
Solved

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

Posted on 2009-05-12
6
Medium Priority
?
763 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 1500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

801 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