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

Posted on 2008-11-05
Last Modified: 2013-12-19
Error seen in log file is OPEN Error
SMTP Server =

We can send email from the server.  We can telnet 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.


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






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


                 DBMS_OUTPUT.put_line ('t_smtpservname from dual' );




Open in new window

Question by:iwalu86
    LVL 73

    Expert Comment

    what is the exception raised from open_connection?  what is v_response?

    Accepted Solution

    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.
    LVL 73

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    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.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    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.
    Viewers will learn how to properly install and use Secure Shell (SSH) to work on projects or homework remotely. Download Secure Shell: Follow basic installation instructions: Open Secure Shell and use "Quick Connect" to enter credentials includi…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now