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!

Bringing JSON To The Table

As a long tine use of Relational Databases I live by the rule that data must be stored in and retrieved from table. And those tables must have columns. But JSON plays by it’s own rules by allowing random attribute-value pairs with no consistency enforced. But the latest version of my most used database, Oracle offers a way to store and retrieve this data in a JSON column in a table; and to retrieve it with a JSON_TABLE function. Here is an example:

-- create a table with a single JSON column
SQL> CREATE TABLE its_json_thanks
 2 ( json_only_please CLOB,
 3 CONSTRAINT json_only
 4 CHECK ( json_only_please IS JSON ) );

Table created.

-- Insert some valid JSON
SQL> INSERT INTO its_json_thanks
 2 VALUES(
 3 '
 4 {
 5 "primary_key" : "1",
 6 "unique_key" : "1"
 7 }');

1 row created.

-- Query the values out
SQL> SELECT pk,
 2 uk
 3 FROM its_json_thanks ijt,
 4 JSON_TABLE(json_only_please,'$'
 5 COLUMNS( pk VARCHAR2(10) PATH '$.primary_key',
 6 uk VARCHAr2(10) PATH '$.unique_key' ))
 7 WHERE ijt.json_only_please.primary_key = '1'
 8 /

PK UK 
---------- ---------- 
1 1

1 row selected

The JSON_TABLE function performs a self-join between the table and the JSON column it contains. It also aligns the attributes (primary key and unique key) with column names (PK and UK).

Oracle quite adept at serving non-tabular data (XML,etc) up in a columnar format for applications that need to see it that way while allowing other applications to interact in their native format.

I’d explain the JSON_TABLE example as a conversation between a maitre’d and a customer like this:

MD: Welcome, Primary JSON. How many in your party?

MRJ: Two please. Me and my wife, Unique.

MD: Ah yes, the famous PK and UK. We have a one dollar,
    two seater near the window for you.

Or maybe not.

Thanks for reading.