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):

--------
Procedure InsertRecord
(
pIdNumber in number
, pDateFiled in varchar2
, pAppType in number
, pTitle in varchar2
, pUserID in varchar2
, pSQLCODE out number
, pSQLERR out varchar2
) is

vExists number;
vAppType number;

begin
-- First, make sure the user has not already added the case by clicking on
-- the Save button twice
select count(field1) into vExists from other_server_vw (AT) develop (DOT) world
where field1= pIdNumber and field2 = 85;

if vExists = 0 then
-- Insert data into table
insert into other_server_vw (AT) develop (DOT) world (field1,field2)
values (85,0);
...
end if;

exception
when others then
pSQLCODE := SQLCODE;
pSQLERR := SQLERRM;
end InsertRecord;

---------

Here is the code that calls this procedure:

--------
InsertRecord(pID,pDateFiled,pAppType,pTitle,pUser, vErrorCode,vErrorMsg);
if vErrorCode > 0 then
pSQLCODE := vErrorCode;
pSQLERR := vErrorMsg;
raise app_error;
end if;
--------

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).

Thank you,
Roger

If the remote server is down, but the remote listener is up, then you'll
get the Oracle Not Available message. In short, you'll get the same
error messages through a db link that you'll see when trying to connect
to the remote db through SQL*Plus.

HTH,
Brian

Roger Moore wrote:
--
================================================== =================

Brian Peasland
dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
the three"

"Roger Moore" <not.me (AT) yahoo (DOT) com> wrote

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@REMOTEDB with logic to retry an
establish a failed connection before saying nay or yea to whether the
link is working or not.


--
Billy