Because the world needs more PL/SQL blogs
The first post in this series created an index using Dot Notation to specify the JSON element to index. Behind the scenes Oracle translated the Dot Notation to a JSON_QUERY call to build the index. Subsequent queries using Dot Notation undergo the same translation to use the index. Can other functions such as JSON_VALUE use … Read more
Welcome to the introductory post of a new series on JSON indexes in the Oracle database. This post shows how JSON indexes are created and used in SQL queries. As always – lets get right to the code! SQL> CREATE TABLE json_data 2 ( json_col CLOB, 3 CONSTRAINT json_only CHECK ( json_col IS JSON ) … Read more
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
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
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 … Read more
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
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
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