Keep JSON in oracle database Keep JSON in oracle database oracle oracle

Keep JSON in oracle database


If it is JSON then it is a string. So I would consider the standard Oracle text types for storing it, for example CLOB like the comments suggested. Also the official Oracle documentation of storing JSON data in Oracle can come handy.


Oracle Database supports JSON data natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike XML data, which is stored using SQL data type XMLType, JSON data is stored in Oracle Database using SQL data types VARCHAR2, CLOB, and BLOB. When possible, Oracle recommends that you use BLOB storage. In particular, doing so obviates the need for any character-set conversion (see JSON: Character Sets and Character Encoding in Oracle Database).

Maximum size of a VARCHAR2 or NVARCHAR2 column is only 4kb. Under 12c, if you have the MAX_STRING_SIZE server property set to EXTENDED, this limit can be increased to 32kb, but still nowhere near 150kb.

Using IS JSON in a Check Constraint to Ensure JSON Data is Strictly Well-Formed (Standard)

CREATE TABLE DTI_REPORT   (id          RAW (16) NOT NULL,    date_loaded TIMESTAMP WITH TIME ZONE,    dti_document CLOB    CONSTRAINT ensure_json CHECK (dti_document IS JSON (STRICT)));INSERT INTO DTI_REPORT VALUES (          SYS_GUID(),          SYSTIMESTAMP,          '{"Person"           : {"name":"Musa","age":45},            "Report"           : "DTI0001",            "Loans"            : {"loan1":{...},"loan2":{...}},            "Salary"           : 4000,            "DTI"           : "30%"}');