Segue From Table Functions To JSON SQL

Its been a while since my last post. There are two reasons for that; one – I’ve said all I wanted to about table functions and such and two – I’ve started on a new direction (tangent?). ¬†JavaScript Object Notation (JSON) and SQL. This post is my segue from the table functions series into a JSON SQL series.

-- create a table with a JSON column
CREATE TABLE json_table
( json_column CLOB,
 CONSTRAINT must_be_json
 CHECK ( json_column IS JSON ) );

-- insert some json data
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "1 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "2 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "3 and 2"} }');

-- create object types for use in the table function
CREATE TYPE json_o AS OBJECT ( json_text VARCHAR2(30) );
/
CREATE TYPE json_t AS TABLE OF json_o;
/

-- and now for the table function
/*--------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION get_json_one ( p_curs SYS_REFCURSOR )
                  RETURN json_t
                  PIPELINED AS
/*--------------------------------------------------------------------*/
  v VARCHAR2(30);
BEGIN
  LOOP
    FETCH p_curs INTO v;
    EXIT WHEN p_curs%NOTFOUND;
    PIPE ROW(json_o(v));
  END LOOP;
END;
/

Then the function is called from a query like this:

SQL> SELECT JSON_VALUE(j.json_text,'$.fieldtwo') two
 2     FROM TABLE(get_json_one(CURSOR(SELECT jt.json_column.fieldone
 3                                      FROM json_table jt))) j
 4 /

TWO
--------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

I’ve already covered table functions a lot in other posts so I’ll just explain the JSON portions of the code. First up is the REF Cursor. When run in SQL*Plus it looks like this:

SQL> SELECT jt.json_column.fieldone
 2 FROM json_table jt;

FIELDONE
------------------------------
{"fieldtwo":"1 and 2"}
{"fieldtwo":"2 and 2"}
{"fieldtwo":"3 and 2"}

3 rows selected.

It uses dot notation to get the value of fieldone from the json column in the table returning it as a JSON Object (squiggly brackets}. The JSON object is then passed to the JSON_VALUE function which extracts the value for fieldtwo.

JSON_VALUE(j.json_text,'$.fieldtwo')

You are probably thinking that is a lot of code to do such a simple thing and you are right. But its a nice way to change over from table functions to JSON SQL.

Here is a much simpler way to do the same thing as the table function.

SQL> SELECT JSON_VALUE(j.f1,'$.fieldtwo') f2
  2    FROM ( SELECT jt.json_column.fieldone AS f1
  3             FROM json_table jt ) j;0

F2
------------------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

The next few posts will explore JSON SQL in more detail.

Thanks for reading!

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!