The ON CHANGE ADD VC Clause

In the past Oracle had a hard and fast rule that Data Manipulation Language (DML) statements such as INSERT could not occur in the same transaction as Data Definition Language (DDL) statements such as ALTER TABLE. For example one could not insert a record into a table while simultaneously adding a column to that table.

It appears that rule has gotten blurry with JSON in Oracle as shown in this example where brand new virtual columns are added to the table as JSON data is inserted.

SQL> --  Create a table with a JSON column
SQL> CREATE TABLE json_data
  2  ( pk_col NUMBER,
  3    json_col CLOB,
  4    CONSTRAINT json_again 
  5       CHECK ( json_col IS JSON ));
Table created.

SQL> -- Create JSON search index being careful to
SQL> -- specify "ON CHANGE ADD VC"
SQL> CREATE SEARCH INDEX json_search ON 
  2  json_data(json_col)
  3  FOR JSON PARAMETERS('SEARCH_ON NONE DATAGUIDE
  4  ON CHANGE ADD_VC');
Index created.
 
SQL> -- Insert some harmless JSON data
SQL> INSERT INTO json_data
  2  VALUES(1,
  3  '{"A_Scalar" : "A_Value",
  4    "B_Scalar" : "B_Value"}');
1 row created.

SQL> -- Insert more harmless JSON data 
SQL> INSERT INTO json_data
  2  VALUES(2,
  3  '{"C_Scalar" : "C_Value",
  4    "C_Scalar" : "C_Value",
  5    "A_Array"  : ["A","B","C"]}');
1 row created.

SQL> -- End the transaction...
SQL> COMMIT;
Commit complete.

SQL> -- Wow - one was added for each new column per
SQL> -- scalar at the top level in the JSON data!
SQL> SELECT column_name,
  2         virtual_column,
  3         hidden_column
  4    FROM user_tab_cols
  5  WHERE table_name = 'JSON_DATA';
COLUMN_NAME                    VIR HID
------------------------------ --- ---
PK_COL                         NO  NO
JSON_COL                       NO  NO
JSON_COL$A_Scalar              YES NO
JSON_COL$B_Scalar              YES NO
JSON_COL$C_Scalar              YES NO
5 rows selected.

SQL> -- The new columns are queryable...
SQL> -- ...in quotes
SQL> SELECT pk_col,
  2         "JSON_COL$A_Scalar",
  3         "JSON_COL$B_Scalar",
  4         "JSON_COL$C_Scalar"
  5    FROM json_data;
    PK_COL JSON_COL JSON_COL JSON_COL
---------- -------- -------- --------
         1 A_Value  B_Value
         2                   C_Value
2 rows selected.

This behaviour will seem very strange to traditional Relational Database developers while barely registering a shrug from JSON aficionado’s. In the next few posts I’l explain how the two camps can peacefully coexist in the same application.

 

Add JSON Virtual Column To A Table

In the November 2017 ODTUG CodeTalk titled JSON in the Oracle Database an attendee asked about adding virtual columns. I deferred answering because the presentation was running long and JSON virtual column are topic all their own. They can be added/removed/used/misused in many ways. Thus a new series of posts is in order!

This first post explains the simplest way to access virtual columns by adding them directly to the table with the JSON column in it. Let’s get right to the code…

SQL> --  Create a table with a JSON column
SQL> CREATE TABLE json_data
  2  ( pk_col NUMBER,
  3    json_col CLOB,
  4    CONSTRAINT json_again  CHECK ( json_col IS JSON ));
Table created.

SQL> -- Insert JSON data
SQL> INSERT INTO json_data
  2  VALUES(1,
  3  '{"Name" : "Marty",
  4    "Reason" : [ { "Medium" : "Movie",
  5        "Title"  : "Madagascar",
  6        "Year"   : 2005 },
  7      { "Medium" : "Movie",
  8        "Title"  : "Madagascar Escape 2 Africa",
  9        "Year"   : 2008 },
 10      { "Medium" : "Movie",
 11        "Title"  : "Madagascar 3 : Europes Most Wanted",
 12        "Year"   : 2012 } ] }');
1 row created.

SQL> -- check what columns table has
SQL> SELECT column_name,
  2      virtual_column,
  3      hidden_column
  4    FROM user_tab_cols
  5  WHERE table_name = 'JSON_DATA';
COLUMN_NAME          VIR HID
-------------------- --- ---
PK_COL               NO  NO
JSON_COL             NO  NO
2 rows selected.

SQL> -- add a JSON Search Index - Note use of ADD_VC
SQL> -- to add virtual columns immediately
SQL> CREATE SEARCH INDEX json_search ON json_data(json_col)
  2  FOR JSON PARAMETERS('SEARCH_ON NONE DATAGUIDE
  3                       ON CHANGE ADD_VC');
Index created.

SQL> -- This index creates a virtual column which gets
SQL> -- added to the table as a "real" column because
SQL> -- ADD_VC was specified for the search index
SQL> -- UPDATE NOV 26, 2017
SQL> -- THIS INDEX IS NOT REQUIRED
SQL> -- CREATE INDEX zebra_name ON json_data jd
SQL> --                        (jd.json_col.Name );

SQL> SELECT column_name,
  2      virtual_column,
  3      hidden_column
  4    FROM user_tab_cols
  5  WHERE table_name = 'JSON_DATA';
COLUMN_NAME          VIR HID
-------------------- --- ---
PK_COL               NO  NO
JSON_COL             NO  NO
JSON_COL$Name        YES NO
SYS_NC00004$         YES YES
4 rows selected.

SQL> -- Use the virtual column in a query
SQL> SELECT "JSON_COL$Name"
  2    FROM json_data  3   WHERE "JSON_COL$Name" = 'Marty';
JSON_COL
--------
Marty
1 row selected.

SQL> -- Yes. The name is case sensitive. 
SQL> -- Its JSON dont'cha know!
SQL> SELECT "JSON_COL$Name"
  2    FROM json_data
  3   WHERE "JSON_COL$NAME" = 'Marty';
WHERE "JSON_COL$NAME" = 'Marty'
*ERROR at line 3:ORA-00904: "JSON_COL$NAME":
invalid identifier

The next post will dig deeper under the covers to see how all of this works.

Thanks for reading!