Saturday, July 22, 2017

JSON in DB 12c: One more reason to upgrade

If you have not upgraded your Oracle database to the 12c version or you are considering database for your new project, than check this neat feature of the Oracle Database 12c. It may outweigh the competitors.


Yes, database 12c supports JSON as a native type, as per me it's been done even better than for the XMLType. I'm no kidding. You get your old table with the old structure and just declare new constraint.
CREATE TABLE  "SNS_LOG_TBL" 
   ( "MESSAGETYPE" VARCHAR2(128) NOT NULL ENABLE, 
 "MESSAGEID" VARCHAR2(1024) NOT NULL ENABLE, 
 "TOPIC" VARCHAR2(1024) NOT NULL ENABLE, 
 "SUBSCRIBER" VARCHAR2(1024), 
 "CONTENT" CLOB, 
  CONSTRAINT "JSON_DATA_CHECK" CHECK (CONTENT IS JSON) ENABLE, 
  CONSTRAINT "SNS_LOG_PK" PRIMARY KEY ("MESSAGEID")
   )
Voila, you used to be a CLOB now a JSON document!
Now, let's play the good old difference game, spy the screenshot below and find all of them.

PL/SQL code in the right browser window would be even worse without the APEX_JSON package, my special thanks to APEX 5 developers. 
Database 12c gives you the same result just with the query; I have kept the PL/SQL block only for the illustration purposes. Ask for me, it's a major feature along with the others to get learned and excited. 

Have the good rest of the weekend, folks. 


Post a Comment