cwbiami
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?
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I REPLACED the RETURNs with NULLs. same error; but thanks for the response
Ex:
Begin
statements.....
Begin
Access remote object
Exception
When others then NULL
End
statements....
Exception (for the upper block and other errors)
statements...
End.