Trap Error for remote objects

Hi All,
I have question on the remote database using db_link.
If the select statement from the remote database is cannot select due to the db link is down.

Is it possble I can Trap the error message just like the select local object does.

BEGIN
SELECT xxx
FROM obj; <<- This is able to trap if this is local object.

EXCEPTION
WHEN OTHERS THEN
do_something_for_me;
END;

If the obj is remote database object, error couldn't trap in the WHEN EXCEPTION....

The error will display asa following
ORA-02068: following severe error from ORA8I
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Any idea ?

Thanks.

Regards,
ckwan

Oracle server will raise an error, if it can not access a object via a database link due to the other database being down or user/password is incorrect. You can capture that error using SQLCODE AND SQLERRM in the exception block OR you can interpret that error and define your own message using RAISE_APPLICATION_ERROR.

Anyway I had found the solution using the pragma exception_init feature. Which is able to capture those error I want.

For example:

declare
db_not_avail exception;
pragma exception_init (db_not_avail,-2048);
begin
insert into abc@remotedb values (....);
exception
when db_not_avail then
do_something;
end;

This is working fine.

Thanks