Trap Error for remote objects
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.
FROM obj; <<- This is able to trap if this is local object.
WHEN OTHERS THEN
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 ?
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.
Last edited by kris109; 01-14-2004 at 12:59 PM.
Remember the Golden Rule - He who has the gold makes the rules!
Ph.D., OCP 8i, 9i, 10g, 11g DBA
Anyway I had found the solution using the pragma exception_init feature. Which is able to capture those error I want.
pragma exception_init (db_not_avail,-2048);
insert into abc@remotedb values (....);
when db_not_avail then
This is working fine.
Click Here to Expand Forum to Full Width