How to solve this issue "distributed update operation failed; rollback required"?-CodePudding

I have a package that calls 2 procedures and I'm using dblink to call them.

I tried running first individually the procedures if it's working and they are both running.

exec proc1@dblink;
exec proc2@dblink;

So when I put them in my package, I'm getting these errors:

ORA-02055: distributed update operation failed; rollback required

ORA-02041: client database did not begin a transaction

I need to call those procedures but how?

CodePudding user response：

Once you commit in the remote environment, you generally can't perform any more DML because the complexities of 2 phase commit. So if your remote procedure did:

procedure P is
begin
  some DML
  commit
  some more DML
end

then when you call it from your local database you're going to hit this.The resolution is to take control of the commits, either by removing them in the procedure or at least making them controllable with a parameter to indicate whether its being called standalone or from a remote caller.

CodePudding user response：

The problem is solved! There was a missing commit in the proc1@dblink.