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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
snimmagaConnect With a Mentor Commented:
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
 
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
 
cwbiamiAuthor Commented:
I REPLACED the RETURNs with NULLs. same error; but thanks for the response
0
All Courses

From novice to tech pro — start learning today.