• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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

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
iwalu86
Asked:
iwalu86
  • 2
1 Solution
 
sdstuberCommented:
what is the exception raised from open_connection?  what is v_response?
0
 
iwalu86Author Commented:
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
 
sdstuberCommented:
ah, good catch!
 I would have tried to help more with some feedback, but I'm glad you got it worked out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now