Transaction Backout Does Not Do JSON Part 2

I did some digging on this issue and found the problem was caused when Transaction Backout executed the following statement which failed:

SQL> INSERT INTO json
 2   VALUES(EMPTY_CLOB());
INSERT INTO json
*
ERROR at line 1:
ORA-02290: check constraint (DRH.KEEP_IT_REAL) violated

At this point I wondered where the EMPTY_CLOB insert was coming from so I added an INSERT trigger to catch the values:

CREATE OR REPLACE TRIGGER check
BEFORE INSERT ON json
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO log
 VALUES(:new.always_json);
 COMMIT;
END;

And sure enough a NULL value (EMPTY_CLOB) wound up in the LOG table.

I was now curious if this was a bug or an actual unsupported feature. I got my answer when I tried to backout this INSERT statement.

INSERT INTO json
VALUES(
'
{
"plch_id" : "1",
"plch_desc" : "One"
}'
);

It raised this error:

ORA-55506: Transaction performed unsupported change

And a quick check of Oracle’s Error Message Guide revealed this:

ORA-55506: Transaction performed unsupported change
Cause: A transaction in the dependency DAG performed some
Action: The specified transaction cannot be backed out.

No, that is not a cut and paste error – the Cause is truncated. I assume it was intended to say “…some unsupported DML.”

The Action makes it official though – Transaction Backout does NOT JSON.

Thanks for reading!

PS: DAG is something called a Directed Acyclic Graph. I may blog about that later.

Transaction Backout Does Not Do JSON

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!