12C

Case Insensitivity with JSON and Oracle 12c?

About a week ago, I was presenting at ECO on JSON parsing in the Oracle 12c Database and an attendee asked a great question. To summarize, Maria Colgan gave a keynote in which she noted a feature in 12c that allows you to define a column as Case Insensitive. The attendee in my session asked if this also applied to JSON in the Oracle database.

There are enough posts on how to configure your database for case insensitivity so I won’t go into details but check out my friend Tim Hall, @oraclebase for some guidance here. I’d also recommend using the Oracle DBCS to try this. It’s just easier. Here we go:

First  let’s spin up a DBCS instance.

dbcs

Now, we need to connect to the database. I’ll use SQL developer. You’ll need to setup the ssh connection.

 

and the database connection.

db text

Let’s connect and test this out. Remember that you will need to do some additional setup on the database to enable this functionality. Check this link here.

Now that we have our database configured, create two (2) tables.

create table

Next we’ll insert the same data in each table.

insert data

 

With our tables populated, we can now run some queries to determine if JSON_VALUE recognizes the case insensitivity on the column.

Test with regular text in a varchar2 defined column

regular text

Test with JSON in a varchar2 defined column

json text

From these examples, it looks as if JSON retains its adherence to Case Sensitivity. I’ll open this post up to discussion.

 

Enjoy!dbaontap

 


Also published on Medium.