Normally when someone asks if it is possible to rollback a single, complete transaction after a commit I have to say no. But this time I was confronted myself with this requirement. We are migrating an Oracle Forms client-server application to Oracle Apex and in one of the forms the transaction-handling was as follows:
- Execute some complex pl/sql that updates/inserts into several tables
- After completion the user can switch between several screens within the same session to look at several parameters.
- If necessary the user can rollback the complete transaction.
Well, in C/S the third step is just a simple ROLLBACK command, but in Apex life is not that simple.
In Apex a commit is issued after every pl/sql block – in our case a Dynamic Action- so step 3 is not that trivial any more. As we saw it we had 2 options:
- Write some complex pl/sql that reverses the complex logic of the first step
- Something else.
I went for “Something else”. The database version is an Oracle 10g EE, so I didn’t have all the new flashback features at hand, but it was enough to make something that works.
I created the following package like this:
create or replace package asl_transaction as procedure capture_transaction ( p_session_id number ); procedure reverse_transaction ( p_session_id number ); procedure confirm_transaction ( p_session_id number ); procedure purge_old_transactions ; end; / create or replace package body asl_transaction as type t_cache is table of rowid index by varchar2(2000) ; g_cache t_cache; procedure capture_transaction ( p_session_id number ) is begin insert into apx_transaction ( session_id , xid , creation_date ) select p_session_id , xid , sysdate from v$transaction where addr = ( select taddr from v$session where sid = ( select sid from v$mystat where rownum = 1 )); end; procedure push_rowid ( p_rowid rowid ) is begin g_cache(p_rowid) := p_rowid; end; function check_rowid ( p_rowid in rowid ) return rowid is begin if g_cache.exists(p_rowid) then return g_cache(p_rowid); else return p_rowid; end if; end; procedure reverse_transaction ( p_session_id number ) is l_new_rowid rowid; begin savepoint inlineTransaction; for r_transactie in ( select xid from apx_transaction where session_id = p_session_id ) loop for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql , row_id from sys.flashback_transaction_query qry where qry.xid = r_transactie.xid and qry.table_name != 'ASL_APX_TRANSACTION' ) loop if upper(r_dml.undo_sql) like '%INSERT INTO%' then push_rowid(r_dml.row_id); l_new_rowid := null; execute immediate r_dml.undo_sql ||' returning rowid into :b1' returning into l_new_rowid; g_cache(r_dml.row_id) := l_new_rowid; else l_new_rowid := check_rowid(r_dml.row_id); if ( l_new_rowid != r_dml.row_id) then r_dml.undo_sql := replace(r_dml.undo_sql,r_dml.row_id, l_new_rowid); end if; execute immediate r_dml.undo_sql; end if; end loop; end loop; confirm_transaction(p_session_id); end; procedure confirm_transaction ( p_session_id number ) is begin delete from apx_transaction where session_id = p_session_id; end; procedure purge_old_transactions is begin delete from apx_transaction where creation_date < sysdate - 1; end; end; /
I did not include the DDL for the table APX_TRANSATION. I think you will be able to reverse engineer the statement.
Before you commit your transaction, so in Apex before the end of your PL/SQL block, you call
The code that is executed by the former Rollback-button now has to be
In my opinion there also has to be some Confirm button that has to call
That’s all. I included a full test script at the end of this post.
For this package to work you’ll have to have give grant select any transaction to the package owner. If you don’t you can’t see the view sys.flashback_transaction_query.
The logic behind the package is this:
- Before the commit, capture all the XID’s from v$transaction of all the DML that are executed in the current transaction
- Save the XID’s with some identifying number, e.g. the session_id
- By reversal, get the undo_sql from flashback_transaction_query and play back the transaction.
- If original transaction deleted the row he first updated c.q. inserted, the reversal has to catch the new rowid of the insert to play back the latter playback c.q. delete.
Some words of caution:
Don’t, really don’t try to access flashback_transaction_query with anything else than a XID.
One of the features you get with an ordinary rollback is locking. The above solution does not involve a locking mechanism, yet. Ghost updates are therefore a possibility: when transaction A updates a table to X, session B updates to Y and A reverses, the update to Y is lost. In the client-server situation session B was blocked for update until A commits or rollbacks. It’s simple Oracle 1:1
However, when we look back at our options we also would have had the same problem with option 1.
Test, test and test if the given solution is appropriate in your case.
drop table emp_2 / drop table dept_2 / delete from apx_transaction where session_id = -1 / CREATE TABLE emp_2 (empno NUMBER PRIMARY KEY, empname VARCHAR2(16), salary NUMBER ) / CREATE TABLE dept_2 (deptno NUMBER, deptname VARCHAR2(32) ) / Prompt Insert in EMP_2 111,mike,555 INSERT INTO emp_2 VALUES (111, 'Mike', 555); COMMIT; prompt insert in dept_2 10,accounting INSERT INTO dept_2 VALUES (10, 'Accounting'); COMMIT; Prompt Prompt Start an erroneous transaction Prompt UPDATE emp_2 SET salary = salary + 100 WHERE empno = 111; INSERT INTO dept_2 VALUES (20, 'Finance'); DELETE FROM emp_2 WHERE empno = 111; Prompt Prompt Capture the transactiondata and commit Prompt execute asl_transaction.capture_transaction(-1); COMMIT; Prompt New data in the tables Prompt EMP_2 select * from emp_2 / Prompt DEPT_2 select * from dept_2 / prompt Prompt The undo-statements begin for r_transactie in ( select xid from apx_transaction) loop for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql , row_id from sys.flashback_transaction_query qry where qry.xid = r_transactie.xid and qry.table_name != 'ASL_GTT_TRANSACTION' ) loop dbms_output.put_line( r_transactie.xid||'...'||r_dml.undo_sql); end loop; end loop; end; / Prompt Prompt Reverse the transaction exec asl_transaction.reverse_transaction(-1) Prompt Prompt Check Prompt Has to be: 111,mike,555 select * from emp_2 / prompt Has to be: 10,accounting select * from dept_2 /