Re: How do I detect a server down in PL/SQL?

"Roger Moore" <not.me_at_yahoo.com> wrote

> I need to detect when an insert fails due to a server being down. I have the
> following code (most of it snipped, names changed to protect the innocent):
<snipped>
> Nothing too fancy. The question I have is: is there a specific error code
> that is raised if the connection to the database link cannot be established?
> There is a known problem with the server we try to connect to, the problem
> being it goes down often. I would like to put that information in the
> procedure so that users know what is happening (ie it's not my fault).

It is a tad more complex.

The 1st time around that your proc gets called, a connection to that db is established. The 2nd time around, that connection is re-used.

Great ito not having pay for a new connection overhead (ito performance and resources) each time.

However, on the 100th time you proc gets called 10 hours after the 1st call that establised the connection, the remote db has decided to pull the plug on you from its side. Maybe you are a DCD victim. Or that db has bounced within the hour that elapsed between your 99th call and 100th call.

Your db is unaware of it. It uses a connection that has been torn up on the remote end. You get an error.

If you immediately execute that SQL again, a new connection will be created (the old one is thrown away) and that same SQL (that just failed a second ago) will work.

Thus any check for "is remote dblink working?" need to take note that the link itself can be stale and broken and that the remote db is indeed available.

I would think a bool function can do what you ask, along the lines of doing something like a SELECT on DUAL_at_REMOTEDB with logic to retry an establish a failed connection before saying nay or yea to whether the link is working or not.

--
Billy