Blog

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

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"

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.