SOLVED

ORA-02041 client database did not begin a transaction

Asked by OracleDba••11 views•oracle

#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
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"
OracleDba•

Post Your Solution