Oracle JSON data type

Informix®

JSON (JavaScript Object Notation) is a widely used standard for data serialization.

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

With Genero BDL, you can use VARCHAR, STRING or TEXT variables, as well as build-in classes such as util.JSON, to store and manipulate 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.

As the size of a JSON object is undefined, you must fetch JSON data into TEXT variables, when the SELECT statement retrieves JSON data without any type conversion.

In order to fetch JSON data into a VARCHAR or STRING variable, convert the JSON data to a character string with the JSON_SERIALIZE() SQL function:

DEFINE k INTEGER
DEFINE s STRING
DECLARE c1 CURSOR FOR
    SELECT pkey, JSON_SERIALIZE(jdoc) FROM tab1 ORDER BY pkey
FOREACH c1 INTO k, s
    DISPLAY k, ": ", NVL(s, "<null>")
END FOREACH

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