0

I have PL/SQL procedure which copies data from remote database. Both databases are 11g. Most of time the procedure is running fine. However, when the remote database has some unexpected problem (like shutdown, etc... but not all the shutdown cause the problem) it cause the procedure hang and do nothing. My procedure looks like below:

Loop
begin
  insert into table as select * from table@remote;
  sleep 20 seconds;
exception
  when others then
     record error...
end;
 end loop;

Since we cannot control the remote database, we cannot use materialize view with fast refresh. This is the only approach we can make.

If the process is hang, we can only kill the process and re-start it. Even for killing the process, the process was marked as killed, not totally killed right away.

The exception statement in the above code does catch some errors but not catch this kind of errors.Is there any way to let the procedure detect the problem by itself and avoid the killing?

Many thanks.

Anuj
  • 197
  • 2
  • 11
  • 2
    How are you recording the error - are you sure you aren't getting one (and then continuing, and thus looping forever)? If you're writing the error to a table but aren't committing (via an autonomous pragma) then you'll never see the message. I'm wondering if explicitly [closing the link](http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin004.htm#ADMIN12168) in the exception block might help, but I'm not at all sure. – Alex Poole Mar 28 '14 at 11:39
  • I understand that you can't use a materialized view with a fast refresh if you cannot create a materialized view log on the source system. Why not use a materialized view that does a complete refresh, though, rather than writing your own? Where does the procedure "hang"? Are you saying that the `INSERT` statement does not return? Or is it somewhere else like when you attempt to commit the changes? – Justin Cave Mar 28 '14 at 14:24

0 Answers0