ORA-02041 client database did not begin a transaction
Kiran Dalvi
10 Nov, 2021
0 Comments
1 Min Read
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
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.