[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-05-12
6
Medium Priority
?
776 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
What is Usenet? There are many different opinions on exactly what Usenet is an isn't. Many opinions are incorrect simply out of ignorance. The Wikipedia listing about Usenet does a good job of explaining it, so instead of repeating it all here I wi…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

656 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