?
Solved

Oracle (UTL_SMTP) erroring with OPEN-Error - Emails for application has completely stopped

Posted on 2008-11-05
3
Medium Priority
?
397 Views
Last Modified: 2013-12-19
Error seen in log file is OPEN Error 127.0.0.1
SMTP Server = 127.0.0.1

We can send email from the server.  We can telnet 127.0.0.1 25 successfully.  This has happend twice in the last 4 days.  Not sure why or how to trouble shoot beyond this point.  At a loss.  There are no errors being logged currently in any log files that I can find or know about.  This application is very important for daily activities.  

There are 16 open connection to port 25 for the user.  It appears something is causing the database to not be dropping the connections.

Has anyone seen a similar behaviour?  We have not had any recent changes to the code or the database.
DECLARE
 
TYPE conninfo IS RECORD(
    conn UTL_SMTP.connection,
    logid NUMBER(10),  -- an ID to keep a log records within a transmission together
    logrec NUMBER(10)  -- a record number to sort all the records within an logid
    );
-- Declare all the Main Variables we use inside the Stored Procedure
 
 
t_smtpservname    tblsystemproperty.propertyvalue%TYPE;
v_response        UTL_SMTP.reply;
conn            conninfo;
 
 
BEGIN					 -- Beginning update max date
procedure  
                
            
 
 
t_smtpservname :=
                system_parms.get_systemparm (system_parms.sysprop_smtpserver); 
                
                DBMS_OUTPUT.put_line ('t_smtpservname from dual' || t_smtpservname);
                
                 v_response :=UTL_SMTP.open_connection (t_smtpservname,system_parms.get_systemparm
(system_parms.sysprop_smtpport),conn.conn);
                 DBMS_OUTPUT.put_line ('t_smtpservname from dual' );
                 
                 
 END

Open in new window

0
Comment
Question by:iwalu86
  • 2
3 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22892671
what is the exception raised from open_connection?  what is v_response?
0
 

Accepted Solution

by:
iwalu86 earned 0 total points
ID: 22998029
We were able to find the cause of this issue.  There was a Control M character in the email address of one recipient.  The stored procedure which raised the open_connection, was not written to gracefully manage errors on an email address and continue, instead it encountered the error opened many connections until we ran out, and then failed.

By removing the bad address we were able to fix the immediate issue.  The dev team has updated the code to better handle errors.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22998118
ah, good catch!
 I would have tried to help more with some feedback, but I'm glad you got it worked out.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

864 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