Solved

ORA-004052 executing trigger

Posted on 1997-08-01
4
766 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 46
Oracle sql query 7 60
PL/SQL Display based on value 4 26
Repeat query 13 46
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 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