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

iwalu86Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
ah, good catch!
 I would have tried to help more with some feedback, but I'm glad you got it worked out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.