Oracle JSON data type

Informix®

Informix supports the BSON and JSON data types, to store JSON documents.

Genero BDL does not provide native built-in types for Informix BSON / JSON. However, it is possible to use large VARCHAR, STRING or TEXT variables, in order to store JSON data.

ORACLE

Oracle 21c introduced support for a native JSON data type:

CREATE TABLE tab1 (
   pkey INTEGER NOT NULL PRIMARY KEY,
   doc1 JSON,
   ...
);
INSERT INTO tab1 values ( 101,
    '{"user":{"creadate":"2023-03-14","scorelist":[700, 650, 720]}}'
);

Solution

The JSON type has been introduced in Oracle 21c, but until Oracle 23c and the dbmora_23 ODI driver, Genero provided only a dbmora_18 driver based on Oracle 18 client without SQLT_JSON type support. Therefore, Genero can only support the new Oracle JSON type with the dbmora_23 ODI driver based on the Oracle 23c instant client, connecting to Oracle server 21c or 23c.

Starting with the dbmora_23 ODI driver, it is possible to use VARCHAR, STRING and TEXT FGL variables as SQL input parameters for JSON columns. However, it is only possible to fetch JSON data into TEXT variables, because the size of a JSON object is undefined.

When extract database schemas with the fgldbsch tool, columns with Oracle JSON type are converted to the FGL TEXT data type.