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

From: Roger Moore <not.me_at_yahoo.com>
Date: Wed, 9 Jul 2003 14:48:10 -0400
Message-ID: <beho1c$57ff9$1@ID-147295.news.dfncis.de>

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.world where field1= pIdNumber and field2 = 85;

 if vExists = 0 then

• Insert data into table insert into other_server_vw_at_develop.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 Received on Wed Jul 09 2003 - 13:48:10 CDT