DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Trap Error for remote objects

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    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

  2. #2
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    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 01:59 PM.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  3. #3
    Join Date
    Oct 2000
    Posts
    250
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width