ORA-004052 executing trigger

We are developing a trigger which updates a remote database. The trigger normally executes fine, however, when the remote database is down the following errors are returned - ORA-004052 error looking up remote object and ORA-010304-oracle not available. We have included an OTHERS exception handler so we should not see any errors during execution. Any ideas on why the error is occuring and how we can work around it?
cwbiamiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snimmagaCommented:
Make sure you include an inner block around the Access statement to Remote database.
Ex:
Begin
statements.....
Begin
Access remote object
Exception
   When others then NULL
End
statements....
Exception (for the upper block and other errors)
statements...
End.
0
cwbiamiAuthor Commented:
question restated:

Oracle v7.1.3.0 w distributed option & SQLnet V1.2
     
     trigger definition
     
     create trigger before update of local table
     when x
     
     declare
       some stuff
       declare a cursor
       BEGIN
     
            fetch into cursor
            WHILE %FOUND Loop
              BEGIN
                 insert into remote table
     
                 EXCEPTION
                 when known remote errors
                   insert into local table
                 when others
                    return        
              END
              fetch next
            ENDLOOP
             close
     
          EXCEPTION
           when others
              return
       END
     
     
     
     The trigger appears to work when no error or insert error in remote
     table. When remote database is down I get
     
     ORA-04052 error looking up remote object remote_table@db_link
     
     ORA-00604 error occurred at recursive sql level 2
     
     ORA-02068 severe error from db_link
     
     ORA-01034 ORACLE not available.
     
     
     is there a fix or workaround for this?
0
snimmagaCommented:
I still see what I said is correct.  Don't use RETURNs in your procedure.  Instead use NULLs, if it should continue without breaking the procedure.  

Again,
BEGIN
insert into remote table
EXCEPTION
 when known remote errors
 insert into local table
 when others

 /****return***/
    DON'T USE RETURN, INSTEAD USE
               WHEN OTHERS THEN NULL

 END
Good luck..

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwbiamiAuthor Commented:
I REPLACED the RETURNs with NULLs. same error; but thanks for the response
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.