- 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.
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.