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.