comment on

Hi Monks,

A problem I'm running in to as I try to write scripts to connect to my employer's Oracle database via DBD::Oracle is that it intermittently fails to connect...

DBI connect(XXX,XXX,XXX) failed: ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach) at lov_lookup.pl line 31.

Or it fails to disconnect...

DBD::Oracle::db disconnect failed: ORA-25408: can not safely replay call (DBD ERROR: OCISessionEnd) at lov_lookup.pl line 58.

After talking to some coworkers who have more database experience than I do, they thought the issue might be that I'm connecting/disconnecting to the DB too often (I'm attempting to get in touch with our Oracle DBA team to ask them as well). My coworkers and I have several different tools they use for DB access, and in all cases those tools will establish a connection and then keep that connection open through however many successive queries are done, only disconnecting after the tool is closed or after a very long timeout period (not sure on exact duration, but from my own experience at least an hour).

With this in mind, I've gone down the path of trying to figure out how I can keep the DB connection alive and reusable between runs of any script I write. The scripts will be run from a local machine Windows environment as either a ".pl" or possibly in the future packaged in to an ".exe". I modified my code to remove the explicit disconnect command, but as I understand it auto calls this when the script finishes anyway (which I don't want). I've also changed the code that establishes the connection to the following based on a similar suggestion in another thread.

my $dsn = "dbi:Oracle:DWAPRD";
my @connection = ($dsn, $user, $pass);
my $dbh = DBI->connect_cached(@connection) or die "Couldn't connect to
+ database: " . DBI->errstr;
[download]

These changes resulted in the previous disconnect error changing to...

DBD::Oracle::db DESTROY failed: ORA-25408: can not safely replay call (DBD ERROR: OCISessionEnd) during global destruction.

It would be convenient if any of the scripts I write could establish the necessary DB connection, but only if necessary, and that all of them could use the existing connection that was left open if still available. I don't know if that's possible... perhaps there is a way to have each script check a file that points to the connection, and if the script has to establish the connection have it update that file? Again, I'm not sure what's possible or not.

I'm hoping that explains where I'm at well enough and the Monks can either give me suggestions on how to accomplish what I'm after, or otherwise explain to me that I'm trying to solve the problem the wrong way and make another suggestion. Thank you.

-------------------------------------------------

UPDATE - Resolved: See update at bottom of post here.

Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

In reply to RESOLVED - Keep a Database Connection Active and Reuse it Between Script Executions? by perldigious

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.