ORA-02041 client database did not begin a transaction

ORA-02041 client database did not begin a transaction
Cause: An update occurred at a coordinated database without the coordinator beginning a distributed transaction. This may happen if a stored procedure commits and then performs updates, and the stored procedure is invoked remotely. It could also happen if an external transaction monitor violates the XA protocol.
Action: If the cause is the former, check that any commit is not followed by an update.

ORA-02041 occurs when we are trying to execute a stored procedure using database link, which perform some dml operation then commits and then again tries to perform another dml.

I am here trying reproduce ORA-02041 with a very simple example. Here we have 2 database,
1) "RemoteDB" which is having a table "remote_table" and a proc "remote_proc" which performs some dml.
2) "LocalDB" which is having a db link "remote" and a proc "local_proc" executing "remote_proc" multiple times.

On RemoteDB, lets create required objects.

SQL> create table remote_table
  2  (
  3  id number,
  4  name varchar2(100)
  5  );
Table created.

SQL> create or replace procedure remote_proc
  2  (
  3     p_id remote_table.id%type,
  4     p_name remote_table.name%type
  5  )
  6  is
  7  begin
  8     insert into remote_table (id) values(p_id);
  9     commit;
 10     update remote_table set name = p_name
 11     where id = p_id;
 12  end;
 13  /
Procedure created.

On LocalDB, lets create its procedure, assuming "remotedb" database link is already created.

SQL> create or replace procedure local_proc
  2  is
  3  begin
  4     nss.remote_proc@remotedb(1,'Amit');
  5     commit;
  6     nss.remote_proc@remotedb(2,'Nimish');
  7     commit;
  8     nss.remote_proc@remotedb(3,'Rahul');
  9     commit;
 10     nss.remote_proc@remotedb(4,'Sachin');
 11     commit;
 12     nss.remote_proc@remotedb(5,'Anuj');
 13     commit;
 14     nss.remote_proc@remotedb(6,'Puru');
 15     commit;
 16  end;
 17  /
Procedure created.

Now lets try to execute "local_proc"

SQL> exec local_proc;
BEGIN local_proc; END;

 *
ERROR at line 1:
ORA-02041: client database did not begin a transaction
ORA-06512: at "IVR1.LOCAL_PROC", line 6
ORA-06512: at line 1

Oh, we have encountered ORA-02041. The reason was simple as we can see in the "remote_proc", we are trying to perform dml after a commit, which is not allowed in a distributed environment.


There are two simple solutions for this
1) Do not commit anywhere in remote_proc

SQL> create or replace procedure remote_proc
  2  (
  3     p_id remote_table.id%type,
  4     p_name remote_table.name%type
  5  )
  6  is
  7  begin
  8     insert into remote_table (id) values(p_id);
  9     
 10     update remote_table set name = p_name
 11     where id = p_id;
 12  end;
 13  /
Procedure created.

2) Commit only at last in remote_proc

SQL> create or replace procedure remote_proc
  2  (
  3     p_id remote_table.id%type,
  4     p_name remote_table.name%type
  5  )
  6  is
  7  begin
  8     insert into remote_table (id) values(p_id);
  9     update remote_table set name = p_name
 10     where id = p_id;
 11      commit; 
 12  end;
 13  /
Procedure created.