asked on

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?

snimmaga

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.

cwbiami

ASKER

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?

ASKER CERTIFIED SOLUTION

snimmaga

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cwbiami

ASKER

I REPLACED the RETURNs with NULLs. same error; but thanks for the response