Solved

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

Posted on 2009-05-12
6
728 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
  • 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 47

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now