I was testing transaction backout with non-scalar datatypes. And since I’m doing JSON research I decided to try JSON columns. I discovered that transaction backout does not work with JSON. Not sure just why yet. Here is the test code I used.
SQL> CREATE TABLE JSON 2 ( always_json CLOB 3 CONSTRAINT keep_it_real 4* CHECK ( always_json IS JSON ) ) Table created. SQL> INSERT INTO json 2 VALUES( 3 ' 4 { 5 "plch_id" : "1", 6 "plch_desc" : "One" 7 }' 8 ); 1 row created. SQL> CREATE OR REPLACE PACKAGE hold_stuff AS 2 v_scn NUMBER; 3 END; 4 / Package created. SQL> BEGIN 2 hold_stuff.v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; 3 END; 4 / PL/SQL procedure successfully completed. SQL> SET TRANSACTION NAME 'DELETE'; Transaction set. SQL> DELETE json; 1 row deleted. SQL> COMMIT; Commit complete. SQL> DECLARE 2 v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('DELETE'); 3 BEGIN 4 DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1, 5 names => v_names, 6 scnhint => hold_stuff.v_scn ); 7 END; 8 / DECLARE * ERROR at line 1: ORA-55511: Flashback Transaction experienced error in executing undo SQL ORA-02290: check constraint (ORA-02290: check constraint (DRH.KEEP_IT_REAL) violated .) violated ORA-06512: at "SYS.DBMS_FLASHBACK", line 51 ORA-06512: at "SYS.DBMS_FLASHBACK", line 86 ORA-06512: at line 4 I'm unable to see what Oracle used for the undo operation (an INSERT) but it will be interesting once I find it. Thanks for reading!