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.