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

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

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.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"