Solved

ORA-004052 executing trigger

Posted on 1997-08-01
4
782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

728 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