the-json-expedition

JSON Check Constraints…or not

by

I’ve been researching the Oracle SQL JSON functions and found some weird interesting behaviors. My New Year’s resolution is to look for the goodness in things so I’ll present them as features and tie them loosely (very, very loosely) to existing Oracle features. Here’s the first entry. The following code imitates a check constraint by requiring an … Read more

JSON Unique Keys

by

When it comes to JSON unique key values there are two schools of thought – those that want keys within objects (squiggly brackets) to be unique and those that don’t care. This can present some interesting outcomes when parsing the data as shown in this post using the SQL JSON_QUERY function. Note that array pointers … Read more

Transaction Backout Does Not Do JSON Part 2

by

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 … Read more

Transaction Backout Does Not Do JSON

by

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 … Read more

Bringing JSON To The Table

by

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 … Read more