Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-004052 executing trigger

Posted on 1997-08-01
4
Medium Priority
?
827 Views
Last Modified: 2008-03-06
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?
0
Comment
Question by:cwbiami
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:snimmaga
ID: 1080405
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
 

Author Comment

by:cwbiami
ID: 1080406
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
 
LVL 5

Accepted Solution

by:
snimmaga earned 200 total points
ID: 1080407
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
 

Author Comment

by:cwbiami
ID: 1080408
I REPLACED the RETURNs with NULLs. same error; but thanks for the response
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question