When Oracle Fusion Middleware calls some PL/SQL API, they generally use global transaction manager and XA framework. Now, in bigger scenario, if the transaction is broken ( issues implicit or explicit COMMIT or ROLLBACK) then we face the ORA-02089.
It is almost next to impossible to detect the error before ST (System Test) or even SIT (System Integration Test). There may be several team involved and your API may use many (if not all) features of them by calling to those modules. There are issue that may not be visible for simple code parser or code reviewer (like SonarSource or Code Xpert), for example dbms_alert and dbms_lock issues implicit commits! so even there is no "commit" in the code directly, still your transaction is broken.
I have written a package with DBMS_XA, which is detecting this types of transaction mismanagement. But there are two things (at least one) I want to add. I need some suggestion whether that is possible at all or not.
1. When a transaction is broken, I got 25352 **** XAER_PROTO: Routine invoked in an improper context, But I want to display ORA-02089: COMMIT is not allowed in a subordinate session. Which seems possible by reading the documentation, but I couldn't manage to do this. https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_xa.htm#BABGAGII
2. Is it possible to track back and point to the location where the transaction is broken ?
I am producing some simple test case here:
My setup:
SCOTT@orclSB 02-FEB-17> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SCOTT@orclSB 02-FEB-17> ed
Wrote file afiedt.buf
1 create or replace procedure p_withcommit
2 as
3 begin
4 insert into foocheck values
5 (100,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')
6 );
7 -- This actually has a commit!!
8 -- The surprising thing is, it is not mentioned in the document.
9 --http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_alert.htm#ARPLS351
10 --Only dbms_alert.waitone issues a commit is mentioned!
11 dbms_alert.register('TestCommit!');
12* end p_withcommit;
SCOTT@orclSB 02-FEB-17> /
Procedure created.
SCOTT@orclSB 02-FEB-17> ed
Wrote file afiedt.buf
1 create or replace procedure p_withoutcommit
2 as
3 begin
4 insert into foocheck values
5 (100,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss')
6 );
7* end p_withoutcommit;
SCOTT@orclSB 02-FEB-17> /
Procedure created.
Now, the first test case there should not be an error, because there is no commit or rollback in the called API.
SCOTT@orclSB 02-FEB-17> ed
Wrote file afiedt.buf
1 declare
2 tranid integer;
3 begin
4 --This XAframework is wrapper of dbms_xa.
5 xaframework.vgprint:=true;
6 --Starting a XA transaction.
7 tranid:=xaframework.startnewtransaction;
8 --Calling my API.
9 p_withoutcommit;
10 --Trying to end XA transaction. Error NOT expected.
11 xaframework.endtransactionwithcommit(tranid);
12* end;
SCOTT@orclSB 02-FEB-17> /
/******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************
*XA* Your transaction has started.. *XA*
*XA* Your transaction ID is: 291 *XA*
******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************/
/******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************
*XA* Your transaction is ended... *XA*
******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************/
/******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************
*XA* Your transaction is commited... *XA*
******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************/
PL/SQL procedure successfully completed.
SCOTT@orclSB 02-FEB-17>
Now, for the second test case, there will be error, and here are my two questions display the ORA-02089 and searching the trace where the transaction has broken.
SCOTT@orclSB 02-FEB-17> ed
Wrote file afiedt.buf
1 declare
2 tranid integer;
3 begin
4 --This XAframework is wrapper of dbms_xa.
5 xaframework.vgprint:=true;
6 --Starting a XA transaction.
7 tranid:=xaframework.startnewtransaction;
8 --Calling my API.
9 p_withcommit;
10 --Trying to end XA transaction. Error NOT expected.
11 xaframework.endtransactionwithcommit(tranid);
12* end;
SCOTT@orclSB 02-FEB-17> /
/******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************
*XA* Your transaction has started.. *XA*
*XA* Your transaction ID is: 827 *XA*
******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************/
/******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************
*XA* Error occurred during XA transaction ending!! *XA*
*XA* Error: 25352 *XA*
*XA* Error: XAER_PROTO: Routine invoked in an improper context *XA*
******* XAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXAXXA **************/
declare
*
ERROR at line 1:
ORA-20007: XAendtranCommitException:- 25352 **** XAER_PROTO: Routine invoked in
an improper context
ORA-06512: at "SCOTT.XAFRAMEWORK", line 204
ORA-06512: at line 11
Please let me know if you need the wrapper on dbms_xa (that is the package XAframework).