dataTypeJson.js

/* Copyright (c) 2015, 2025, Oracle and/or its affiliates. */

/******************************************************************************
 *
 * This software is dual-licensed to you under the Universal Permissive License
 * (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
 * 2.0 as shown at https://www.apache.org/licenses/LICENSE-2.0. You may choose
 * either license.
 *
 * If you elect to accept the software under the Apache License, Version 2.0,
 * the following applies:
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *    https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * NAME
 *   244.dataTypeJson.js
 *
 * DESCRIPTION
 *   Testing Oracle data type support - JSON.
 *
 *****************************************************************************/
'use strict';

const oracledb  = require('oracledb');
const assert    = require('assert');
const assist    = require('./dataTypeAssist.js');
const dbConfig  = require('./dbconfig.js');
const testsUtil = require('./testsUtil.js');

describe('244.dataTypeJson.js', function() {

  let connection;
  let isRunnable = false;
  let isOracle_23_4 = false;
  let isOracle_23_6 = false;
  const tableName = "nodb_json";

  const jsonVals = assist.jsonValues;
  const default_stmtCacheSize = oracledb.stmtCacheSize;

  before(async function() {
    connection = await oracledb.getConnection(dbConfig);

    if (testsUtil.getClientVersion() >= 2100000000 && connection.oracleServerVersion >= 2100000000) {
      isRunnable = true;
    }

    // Check if we are running the latest Oracle Server and Client versions
    // for vector and long field names support
    isOracle_23_4 = connection.oracleServerVersion >= 2304000000
      && (oracledb.thin || oracledb.oracleClientVersion >= 2304000000);

    // Check if we are running the latest Oracle Server and Client versions
    // for Sparse vector
    isOracle_23_6 = connection.oracleServerVersion >= 2306000000
      && (oracledb.thin || oracledb.oracleClientVersion >= 2306000000);

    if (!isRunnable) {
      this.skip();
    }

  }); // before()

  after(async function() {
    await connection.close();
  });  // after()

  describe('244.1 testing JSON data in various lengths', function() {

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = 0;
      await assist.setUp(connection, tableName, jsonVals);
    });  // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.1.1 SELECT query', async function() {
      await assist.dataTypeSupport(connection, tableName, jsonVals);
    }); // 244.1.1

    it('244.1.2 resultSet stores JSON data correctly', async function() {
      await assist.verifyResultSet(connection, tableName, jsonVals);
    }); // 244.1.2

    it('244.1.3 works well with REF Cursor', async function() {
      await assist.verifyRefCursor(connection, tableName, jsonVals);
    }); // 244.1.3

    it('244.1.4 columns fetched from REF CURSORS can be mapped by fetchInfo settings', async function() {
      await assist.verifyRefCursorWithFetchInfo(connection, tableName, jsonVals);
    }); // 244.1.4

    it('244.1.5 Negative field name length > 255 bytes - Oracle 21c', async function() {
      if (isOracle_23_4 || !oracledb.thin) {
        this.skip();
      }
      // The server does not throw an error for out-of-bounds field length
      // names as of now.
      const sequence = 1;
      const longFieldName = 'A'.repeat(256);
      const jsonVal = {};
      jsonVal[longFieldName] = "2018/11/01 18:30:00";
      const sql = "insert into " + tableName + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];
      await assert.rejects(
        async () => await connection.execute(sql, binds),
        // NJS-114: OSON field names may not exceed %d UTF-8 encoded bytes
        /NJS-114:/
      );
    }); // 244.1.5

  }); // 244.1

  describe('244.2 stores null value correctly', function() {

    it('244.2.1 testing Null, Empty string and Undefined', async function() {
      await assist.verifyNullValues(connection, tableName);
    }); // 244.2.1

  }); // 244.2

  describe('244.3 testing JSON with executeMany()', function() {

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }

      oracledb.fetchAsBuffer = [oracledb.BLOB];
      const sql = " CREATE TABLE " + tableName +
        " ( \n" +
        " id         NUMBER, \n" +
        " content    JSON, \n" +
        " osonCol    BLOB, \n" +
        " constraint Oson_ck_1 check (OsonCol is json format oson)" +
        " )";
      await testsUtil.createTable(connection, tableName, sql);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.fetchAsBuffer = [];
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.3.1 works with executeMany()', async function() {
      const jsonVal1 = [1, 2, 3] ;
      const jsonVal2 = {"fred": 5, "george": 6};
      const jsonVal3 = Buffer.from("A Raw");
      const jsonVal4 = "json_scalar(to_blob(utl_raw.cast_to_raw('A short BLOB')))";
      const jsonVal5 = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date()
      };
      const jsonVal6 = { "key1": 1 };
      const jsonVal7 = { "key2": -3.1415 };
      const jsonVal8 = { "key3": false };
      const jsonVal9 = { "key4": null };
      const jsonVal10 = { "key5": "2018/11/01 18:30:00" };
      const jsonVal11 = { "key6": [1, 2, 3, 99] };
      const jsonVal12 = { "key7": ["json array1", "json array2"], "key8": [true, false] };
      const jsonVal13 = { "key9": "#$%^&*()@!~`-+=" };
      const jsonVal14 = { "key10": "_:;?><,.|/" };
      const jsonVal15 = { "key11": "Math.pow(2, 53) -1" };
      const jsonVal16 = { "key12": "-Math.pow(2, 53) -1" };
      const jsonVal17 = { "key13": {"key13-1": "value13-1", "key13-2": "value13-2"} };
      const jsonVal18 = { "#$%^&*()@!~`-+=": "special key14 name" };
      const jsonVal19 = [new Float32Array([1.23, 4.43, -12.13]), [1, 2]];
      const jsonVal20 = {
        KeyF32: new Float32Array([1, 2]),
        KeyF64: new Float64Array([-992.1, 994.3]),
        KeyInt8: new Int8Array([-123, 12, 123]),
        keyBuf: Buffer.from("A Raw")
      };
      const jsonVal21 = {
        KeyF32: new Float32Array([1, 2]),
        KeyF64: new Float64Array([-992.1, 994.3]),
        KeyInt8: new Int8Array([-123, 12, 123]),
        KeyBinary: new Uint8Array([240, 120]),
        keyBuf: Buffer.from("A Raw")
      };
      const jsonVal22 = { "key22": [new Uint8Array([20, 10])]};
      const jsonVal23 = {
        "key23": new oracledb.IntervalYM({ years: 5, months: 9 })
      };
      const jsonVal24 = {
        "key24": new oracledb.IntervalDS({ days: 5, hours: 10, minutes: 30,
          seconds: 15, fseconds: 123 })
      };
      const sparseRowEntry = 25;
      const jsonVal25 = {
        "key25": [new oracledb.SparseVector(
          {
            values: new Float64Array([-992.1, 994.3]),
            indices: new Uint32Array([0, 2]),
            numDimensions: 4
          })]
      };
      const binds = [
        [1, jsonVal1],
        [2, jsonVal2],
        [3, jsonVal3],
        [4, jsonVal4],
        [5, jsonVal5],
        [6, jsonVal6],
        [7, jsonVal7],
        [8, jsonVal8],
        [9, jsonVal9],
        [10, jsonVal10],
        [11, jsonVal11],
        [12, jsonVal12],
        [13, jsonVal13],
        [14, jsonVal14],
        [15, jsonVal15],
        [16, jsonVal16],
        [17, jsonVal17],
        [18, jsonVal18]
      ];

      // Inserting TypedArrays is only allowed with Oracle Database 23ai and
      // Oracle Client 23ai versions and above
      if (isOracle_23_4) {
        binds.push([19, jsonVal19]);
        binds.push([20, jsonVal20]);
      }
      if (await testsUtil.isVectorBinaryRunnable()) {
        binds.push([21, jsonVal21]);
        binds.push([22, jsonVal22]);
      }
      // push interval datatypes in JSON
      binds.push([23, jsonVal23]);
      binds.push([24, jsonVal24]);

      if (isOracle_23_6) {
        binds.push([25, jsonVal25]);
      }

      binds.forEach((element, index) => {
        binds[index].push(connection.encodeOSON(element[1]));
      });
      let sql = "INSERT INTO " + tableName + " VALUES (:1, :2, :3)";
      const options = {
        autoCommit: true,
        bindDefs: [
          { type: oracledb.NUMBER },
          { type: oracledb.DB_TYPE_JSON },
          { type: oracledb.DB_TYPE_BLOB }
        ]
      };
      let result = await connection.executeMany(sql, binds, options);
      assert.strictEqual(result.rowsAffected, binds.length);
      sql = `SELECT * FROM ${tableName} where id < ${sparseRowEntry} ORDER BY id`;
      result = await connection.execute(sql);
      let retRows = result.rows;
      retRows.forEach((element, index) => {

        assert.deepStrictEqual(binds[index][1], connection.decodeOSON(element[2]));
        assert.deepStrictEqual(binds[index][0], element[0]);
        assert.deepStrictEqual(binds[index][1], element[1]);
      });
      if (isOracle_23_6) {
        sql = `SELECT * FROM ${tableName} where id >= ${sparseRowEntry} ORDER BY id`;
        result = await connection.execute(sql);
        retRows = result.rows;
        retRows.forEach((element, index) => {
          const sparseData = connection.decodeOSON(element[2]);
          const sparseBindsIndex = index + sparseRowEntry - 1;
          assert.deepStrictEqual(JSON.stringify(binds[sparseBindsIndex][1]), JSON.stringify(sparseData));
          assert.deepStrictEqual(binds[sparseBindsIndex][0], element[0]);
          assert.deepStrictEqual(JSON.stringify(binds[sparseBindsIndex][1]), JSON.stringify(element[1]));
        });
      }
    }); // 244.3.1

  }); // 244.3

  describe('244.4 testing JSON with PL/SQL procedure BIND_IN and BIND_OUT', function() {
    const proc_in_name = "nodb_json_plsql_proc_in";
    const proc_out_name = "nodb_json_plsql_proc_out";
    const proc_in = "CREATE OR REPLACE PROCEDURE " + proc_in_name + " (ID IN NUMBER, inValue IN JSON )\n" +
        "AS \n" +
        "BEGIN \n" +
        "    insert into " + tableName + " ( num, content ) values (ID, inValue); \n" +
        "END " + proc_in_name + "; ";
    const run_proc_in = "BEGIN " + proc_in_name + " (:i, :c); END;";
    const drop_proc_in = "DROP PROCEDURE " + proc_in_name;
    const proc_out = "CREATE OR REPLACE PROCEDURE " + proc_out_name + " (ID IN NUMBER, outValue OUT JSON)\n" +
        "AS \n" +
        "BEGIN \n" +
        "    select content into outValue from " + tableName + " where num = ID; \n" +
        "END " + proc_out_name + "; ";
    const run_proc_out = "BEGIN " + proc_out_name + " (:i, :c); END;";
    const drop_proc_out = "DROP PROCEDURE " + proc_out_name;

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = 0;
      await assist.setUp(connection, tableName, jsonVals);
      await connection.execute(proc_in);
      await connection.execute(proc_out);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await connection.execute(drop_proc_in);
      await connection.execute(drop_proc_out);
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.4.1 bind by name', async function() {
      const sequence = 100;
      const jsonVal = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date(),
        keyJ: new oracledb.IntervalYM({ years: 5, months: 9 }), // Interval year-to-month
        keyK: new oracledb.IntervalDS({ days: 5, hours: 8, minutes: 30, seconds: 15 }) // Interval day-to-second
      };
      let binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN, maxSize: 1000 }
      };
      await connection.execute(run_proc_in, binds);
      binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 2000 }
      };
      const result = await connection.execute(run_proc_out, binds);
      assert.deepStrictEqual(result.outBinds.c, jsonVal);
    }); // 244.4.1

    it('244.4.2 bind by position', async function() {
      const sequence = 101;
      const jsonVal = { "key13": {"key13-1": "value13-1", "key13-2": "value13-2"} };
      let binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN, maxSize: 10 }
      ];
      await connection.execute(run_proc_in, binds);
      binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 10 }
      ];
      const result = await connection.execute(run_proc_out, binds);
      assert.deepStrictEqual(result.outBinds[0], jsonVal);
    }); // 244.4.2
  });  // 244.4

  describe('244.5 testing JSON with PL/SQL procedure BIND_INOUT', function() {
    const proc_name = "nodb_json_proc_inout";
    const proc = "CREATE OR REPLACE PROCEDURE " + proc_name + " (ID IN NUMBER, inoutValue IN OUT JSON)\n" +
        "AS \n" +
        "BEGIN \n" +
        "    insert into " + tableName + " ( num, content ) values (ID, inoutValue); \n" +
        "    select content into inoutValue from " + tableName + " where num = ID; \n" +
        "END " + proc_name + "; ";
    const sqlRun = "BEGIN " + proc_name + " (:i, :c); END;";
    const proc_drop = "DROP PROCEDURE " + proc_name;

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = 0;
      await assist.setUp(connection, tableName, jsonVals);
      await connection.execute(proc);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await connection.execute(proc_drop);
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.5.1 bind by name', async function() {
      const sequence = 100;
      const jsonVal = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date()
      };
      const binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_INOUT, maxSize: 2000 }
      };
      const result = await connection.execute(sqlRun, binds);
      assert.deepStrictEqual(result.outBinds.c, jsonVal);
    }); // 244.5.1

    it('244.5.2 bind by position', async function() {
      const sequence = 101;
      const jsonVal = {"fred": 5, "george": 6};
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_INOUT, maxSize: 10 }
      ];
      const result = await connection.execute(sqlRun, binds);
      assert.deepStrictEqual(result.outBinds[0], jsonVal);
    }); // 244.5.2

  }); // 244.5

  describe('244.6 testing JSON with PL/SQL function BIND_IN and BIND_OUT', function() {
    const fun_name_in = "nodb_json_fun_in";
    const fun_name_out = "nodb_json_fun_out";
    const proc_in = "CREATE OR REPLACE FUNCTION " + fun_name_in + " (ID IN NUMBER, inValue IN JSON) RETURN JSON\n" +
        "IS \n" +
        "    tmpvar JSON; \n" +
        "BEGIN \n" +
        "    insert into " + tableName + " ( num, content ) values (ID, inValue); \n" +
        "    select content into tmpvar from " + tableName + " where num = ID; \n" +
        "    RETURN tmpvar; \n" +
        "END ; ";
    const run_proc_in = "BEGIN :output := " + fun_name_in + " (:i, :c); END;";
    const drop_proc_in = "DROP FUNCTION " + fun_name_in;

    const proc_out = "CREATE OR REPLACE FUNCTION " + fun_name_out + " (ID IN NUMBER, outValue OUT JSON) RETURN NUMBER\n" +
        "IS \n" +
        "    tmpvar NUMBER; \n" +
        "BEGIN \n" +
        "    select num, content into tmpvar, outValue from " + tableName + " where num = ID; \n" +
        "    RETURN tmpvar; \n" +
        "END ; ";
    const run_proc_out = "BEGIN :output := " + fun_name_out + " (:i, :c); END;";
    const drop_proc_out = "DROP FUNCTION " + fun_name_out;

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = 0;
      await assist.setUp(connection, tableName, jsonVals);
      await connection.execute(proc_in);
      await connection.execute(proc_out);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await connection.execute(drop_proc_in);
      await connection.execute(drop_proc_out);
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.6.1 bind by name', async function() {
      const sequence = 100;
      const jsonVal = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date()
      };
      let binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN, maxSize: 1000 },
        output: { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT }
      };
      let result = await connection.execute(run_proc_in, binds);
      assert.deepStrictEqual(result.outBinds.output, jsonVal);
      binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 2000 },
        output: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT }
      };
      result = await connection.execute(run_proc_out, binds);
      assert.deepStrictEqual(result.outBinds.c, jsonVal);
      assert.deepStrictEqual(result.outBinds.output, sequence);
    }); // 244.6.1

    it('244.6.2 bind by position', async function() {
      const sequence = 101;
      const jsonVal = { "key13": {"key13-1": "value13-1", "key13-2": "value13-2"} };
      let binds = [
        { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT },
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN, maxSize: 10 }
      ];
      let result = await connection.execute(run_proc_in, binds);
      assert.deepStrictEqual(result.outBinds[0], jsonVal);
      binds = [
        { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 10 }
      ];
      result = await connection.execute(run_proc_out, binds);
      assert.deepStrictEqual(result.outBinds[1], jsonVal);
      assert.deepStrictEqual(result.outBinds[0], sequence);
    }); // 244.6.1

  }); // 244.6

  describe('244.7 testing JSON with PL/SQL function BIND_INOUT', function() {
    const proc_name = "nodb_json_proc_inout";
    const proc = "CREATE OR REPLACE PROCEDURE " + proc_name + " (ID IN NUMBER, inoutValue IN OUT JSON)\n" +
        "AS \n" +
        "BEGIN \n" +
        "    insert into " + tableName + " ( num, content ) values (ID, inoutValue); \n" +
        "    select content into inoutValue from " + tableName + " where num = ID; \n" +
        "END " + proc_name + "; ";
    const sqlRun = "BEGIN " + proc_name + " (:i, :c); END;";
    const proc_drop = "DROP PROCEDURE " + proc_name;

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = 0;
      await assist.setUp(connection, tableName, jsonVals);
      await connection.execute(proc);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await connection.execute(proc_drop);
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.7.1 bind by name', async function() {
      const sequence = 100;
      const jsonVal = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date()
      };
      const binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_INOUT, maxSize: 2000 }
      };
      const result = await connection.execute(sqlRun, binds);
      assert.deepStrictEqual(result.outBinds.c, jsonVal);
    }); // 244.7.1

    it('244.7.2 bind by position', async function() {
      const sequence = 101;
      const jsonVal = {"fred": 5, "george": 6};
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_INOUT, maxSize: 10 }
      ];
      const result = await connection.execute(sqlRun, binds);
      assert.deepStrictEqual(result.outBinds[0], jsonVal);
    }); // 244.7.2

  }); // 244.7

  describe('244.8 testing JSON with DML returning into', function() {

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      const sql = " CREATE TABLE " + tableName + " ( \n" +
        "  num        NUMBER, \n" +
        "  content    JSON \n" +
        "  )";
      await testsUtil.createTable(connection, tableName, sql);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    it('244.8.1 bind by name', async function() {
      const sequence = 1;
      const jsonVal = {
        keyA: 8,
        keyB: "A String",
        keyC: Buffer.from("A Raw"),
        keyD: true,
        keyE: false,
        keyF: null,
        keyG: true,
        keyH: [ 9, 10, 11 ],
        keyI: new Date()
      };

      const sql = "insert into " + tableName + " ( num, content ) values (:i, :c) returning content into :output";
      const binds = {
        i: { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        c: { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN },
        output: { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 2000 }
      };
      const result = await connection.execute(sql, binds);
      assert.deepStrictEqual(result.outBinds.output[0], jsonVal);
    }); // 244.8.1

    it('244.8.2 bind by position', async function() {
      const sequence = 2;
      const jsonVal = { "key5": "2018/11/01 18:30:00" };

      const sql = "insert into " + tableName + " ( num, content ) values (:i, :c) returning content into :output";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN },
        { type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_OUT, maxSize: 2000 }
      ];
      const result = await connection.execute(sql, binds);
      assert.deepStrictEqual(result.outBinds[0][0], jsonVal);
    }); // 244.8.2

  }); // 244.8

  describe('244.9 testing JSON with oracledb.fetchAsString and fetchInfo oracledb.STRING', function() {

    let sequence = 1;
    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      const sql = " CREATE TABLE " + tableName + " ( \n" +
          " id         NUMBER, \n" +
          " content    JSON \n" +
          " )";
      await testsUtil.createTable(connection, tableName, sql);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      oracledb.fetchAsString = [];
      await testsUtil.dropTable(connection, tableName);
    }); // after()

    const testInsertAndFetch = async function(seq, jsonVal, resultStr, selectOpts) {
      let sql = "insert into " + tableName + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: seq, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + tableName + " where id = " + seq;
      const result = await connection.execute(sql, [], selectOpts);
      assert.strictEqual(typeof result.rows[0][0], 'string');
      assert.strictEqual(result.rows[0][0].length, resultStr.length);
      assert.strictEqual(result.rows[0][0], resultStr);
    };

    it('244.9.1 works with oracledb.fetchAsString', async function() {
      oracledb.fetchAsString = [ oracledb.DB_TYPE_JSON ];
      const jsonVals = [{ "key5": "2018/11/01 18:30:00" }];
      const resultStr = ["{\"key5\":\"2018/11/01 18:30:00\"}"];

      // Add the JSON Field with Long Field Name to the JSON Values Array
      // for Oracle DB 23.4 (and Oracle Client 23.4)
      if (isOracle_23_4) {
        const longFieldName = 'A'.repeat(1000);
        const jsonVal = {};
        jsonVal[longFieldName] = "2018/11/01 18:30:00";
        jsonVals.push(jsonVal);
        resultStr.push(`{"${longFieldName}":"2018/11/01 18:30:00"}`);
      }

      for (let i = 1; i <= jsonVals.length; i++) {
        await testInsertAndFetch(sequence, jsonVals[i - 1], resultStr[i - 1], {});
        sequence++;
      }
    }); // 244.9.1

    it('244.9.2 could work with fetchInfo oracledb.STRING', async function() {
      oracledb.fetchAsString = [];
      const jsonVal = { "key5": "2018/11/01 18:30:00" };
      const resultStr = "{\"key5\":\"2018/11/01 18:30:00\"}";

      const options = {
        fetchInfo: { C: { type: oracledb.STRING } }
      };

      // Test Insert and Fetch of JSON Data
      await testInsertAndFetch(sequence, jsonVal, resultStr, options);
      sequence++;

    }); // 244.9.2

  }); // 244.9

  describe('244.10 testing JSON with long field names > 255 bytes', function() {

    const table = 'nodb_json_long';
    let sequence = 1;

    before('create table, insert data', async function() {
      if (!isOracle_23_4) {
        this.skip();
      }
      const sql = " CREATE TABLE " + table + " ( \n" +
        " id         NUMBER, \n" +
        " content    JSON \n" +
        " )";
      await testsUtil.createTable(connection, table, sql);
    }); // before()

    after(async function() {
      if (!isOracle_23_4) {
        return;
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      oracledb.fetchAsString = [];
      await testsUtil.dropTable(connection, table);
    }); // after()

    it('244.10.1 single long JSON field name', async function() {
      oracledb.fetchAsString = [ oracledb.DB_TYPE_JSON ];
      const longFieldName = 'A'.repeat(1000);
      const jsonVal = {};
      jsonVal[longFieldName] = "2018/11/01 18:30:00";
      const resultStr = `{"${longFieldName}":"2018/11/01 18:30:00"}`;

      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.strictEqual(typeof result.rows[0][0], 'string');
      assert.strictEqual(result.rows[0][0].length, resultStr.length);
      assert.strictEqual(result.rows[0][0], resultStr);
      sequence++;

    }); // 244.10.1

    it('244.10.2 multiple long JSON field names', async function() {
      oracledb.fetchAsString = [];

      const jsonVal = {};
      const NO_OF_ALPHABETS = 26;
      for (let i = 0; i < NO_OF_ALPHABETS; i++) {
        for (let j = 0; j < NO_OF_ALPHABETS; j++) {
          const longFieldName = String.fromCharCode('A'.charCodeAt(0) + i) +
            String.fromCharCode('A'.charCodeAt(0) + j) + 'X'.repeat(500);
          jsonVal[longFieldName] = i + j;
        }
      }

      // Testing multi-byte field names and multi-byte field values
      const multiByteLongFieldName = '𠜎'.repeat(1000);
      jsonVal[multiByteLongFieldName] = '𠜎𠜎𠜎𠜎𠜎';

      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.deepStrictEqual(result.rows[0][0], jsonVal);
      sequence++;

    }); // 244.10.2

    it('244.10.3 multiple long and short JSON field names', async function() {
      oracledb.fetchAsString = [];

      const jsonVal = {};
      const NO_OF_ALPHABETS = 26;
      for (let i = 0; i < NO_OF_ALPHABETS; i++) {
        for (let j = 0; j < NO_OF_ALPHABETS; j++) {
          const shortFieldName = String.fromCharCode('A'.charCodeAt(0) + i) +
            String.fromCharCode('A'.charCodeAt(0) + j);
          jsonVal[shortFieldName] = 6.75;
          const longFieldName = String.fromCharCode('A'.charCodeAt(0) + i) +
            String.fromCharCode('A'.charCodeAt(0) + j) + 'X'.repeat(254);
          jsonVal[longFieldName] = i + j;
        }
      }

      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.deepStrictEqual(result.rows[0][0], jsonVal);
      sequence++;

    }); // 244.10.3

    it('244.10.4 negative case for out-of-bounds field length names', async function() {
      // The server does not throw an error for out-of-bounds field length
      // names as of now.
      if (!oracledb.thin)
        this.skip();
      const longFieldName = 'A'.repeat(65536);
      const jsonVal = {};
      jsonVal[longFieldName] = "2018/11/01 18:30:00";
      const sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];
      await assert.rejects(
        async () => await connection.execute(sql, binds),
        // NJS-114: OSON field names may not exceed %d UTF-8 encoded bytes
        /NJS-114:/
      );
      sequence++;
    }); // 244.10.4

  }); // 244.10

  describe('244.11 testing compressed JSON with relative offsets', function() {
    // Relative offsets enable the offset values in the OSON format to be
    // much smaller and also allow for repeated values, which lends itself
    // well to compression.

    const table = 'nodb_json_rel_offsets';
    let sequence = 1;

    before('create table, insert data', async function() {
      if (!isOracle_23_4) {
        this.skip();
      }
      const sql = " CREATE TABLE " + table + " ( \n" +
          " id         NUMBER, \n" +
          " content    JSON \n" +
          " ) JSON (content) STORE AS (COMPRESS HIGH)";
      await testsUtil.createTable(connection, table, sql);
    }); // before()

    after(async function() {
      if (!isOracle_23_4) {
        return;
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      oracledb.fetchAsString = [];
      await testsUtil.dropTable(connection, table);
    }); // after()

    it('244.11.1 fetch JSON with relative offsets', async function() {
      const longFieldName = 'A'.repeat(1000);
      const jsonVal = {};
      jsonVal[longFieldName] = "2018/11/01 18:30:00";
      jsonVal['num_list'] = [1.5, 2.25, 3.75, 5.5];
      jsonVal['str_list'] = ["string 1", "string 2"];

      // Send a JSON string, which is converted and stored as compressed JSON
      // by the database
      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: JSON.stringify(jsonVal) }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.deepStrictEqual(result.rows[0][0], jsonVal);
      sequence++;

    }); // 244.11.1

    it('244.11.2 fetch JSON with relative offsets and shared fields and values', async function() {
      const jsonVal = [];
      for (let i = 0; i < 15; i++) {
        jsonVal.push({a: 6711, b: 'String value'});
      }

      // Send a JSON string, which is converted and stored as compressed JSON
      // by the database
      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: JSON.stringify(jsonVal) }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.deepStrictEqual(result.rows[0][0], jsonVal);
      sequence++;

    }); // 244.11.2

    it('244.11.3 fetch JSON with relative offsets and shared fields, not values', async function() {
      const jsonVal = [];
      for (let i = 0; i < 15; i++) {
        jsonVal.push({a: 6711 + i, b: 'String value ' + i});
      }

      // Send a JSON string, which is converted and stored as compressed JSON
      // by the database
      let sql = "insert into " + table + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: sequence, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: JSON.stringify(jsonVal) }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + table + " where id = " + sequence;
      const result = await connection.execute(sql);
      assert.deepStrictEqual(result.rows[0][0], jsonVal);
      sequence++;

    }); // 244.11.3

  }); // 244.11

  describe('244.12 Verify auto-generated SODA document key', function() {
    const TABLE = 'nodb_244_63soda';
    let supportsJsonId;

    before('create table, insert data', async function() {
      supportsJsonId = (testsUtil.getClientVersion() >= 2304000000) &&
        (connection.oracleServerVersion >= 2304000000);
      if (!supportsJsonId) {
        this.skip();
      }
      const sql = `CREATE JSON COLLECTION TABLE if not exists ${TABLE}`;
      await connection.execute(sql);
    }); // before()

    after(async function() {
      if (!supportsJsonId) {
        return;
      }
      const sql = `DROP TABLE if exists ${TABLE}`;
      await connection.execute(sql);
    }); // after()

    it('244.12.1 Verify Json Id on select', async function() {
      const inpDoc = {"name": "Jenny"};
      let sql = ` insert into ${TABLE} values (:1)`;
      let result = await connection.execute(sql, [{
        type: oracledb.DB_TYPE_JSON,
        val: inpDoc
      }]);

      // Verify _id is generated.
      sql = `select * from ${TABLE}`;
      result = await connection.execute(sql);
      let genDoc = result.rows[0][0];
      assert(("_id" in genDoc));
      const autogenID = genDoc._id;

      // Verify update with new values without passing _id.
      inpDoc.name = "Scott";
      sql = ` update ${TABLE} set DATA = :1`;
      result = await connection.execute(sql, [{
        type: oracledb.DB_TYPE_JSON,
        val: inpDoc
      }]);
      sql = `select * from ${TABLE}`;
      result = await connection.execute(sql);
      genDoc = result.rows[0][0];
      const updatedID = genDoc._id;
      assert.deepStrictEqual(updatedID, autogenID);
      assert.strictEqual(inpDoc.name, genDoc.name);

      // Verify update with new values with passing _id from the generated Doc.
      genDoc.name = "John";
      sql = ` update ${TABLE} set DATA = :1`;
      result = await connection.execute(sql, [{
        type: oracledb.DB_TYPE_JSON,
        val: genDoc
      }]);
      sql = `select * from ${TABLE}`;
      result = await connection.execute(sql);
      const updatedDoc = result.rows[0][0];
      assert.deepStrictEqual(updatedDoc, genDoc);
      const expectedJsonData = genDoc;
      expectedJsonData._id = Buffer.from(autogenID).toString('hex');
      assert.deepStrictEqual(JSON.stringify(expectedJsonData),
        JSON.stringify(updatedDoc));

      // Insert Document with Previously generated JsonId type.
      const jsonId = new oracledb.JsonId(genDoc._id);
      const inpDocWithJsonIdKey = {"_id": jsonId, "name": "Bob"};
      sql = ` insert into ${TABLE} values (:1)`;
      result = await connection.execute(sql, [{
        type: oracledb.DB_TYPE_JSON,
        val: inpDocWithJsonIdKey
      }]);
      sql = `select * from ${TABLE}`;
      result = await connection.execute(sql);
      genDoc = result.rows[1][0];
      assert.deepStrictEqual(genDoc, inpDocWithJsonIdKey);

      // overwrite the auto-generated _id with user key should fail.
      genDoc._id = "RandomId";
      sql = ` update ${TABLE} set DATA = :1`;
      await assert.rejects(
        async () => await connection.execute(sql, [{
          type: oracledb.DB_TYPE_JSON,
          val: genDoc
        }]),
        /ORA-54059:/ // cannot update an immutable column to a different value
      );

      // User provided keys should still work.
      const inpDocWithUserKey = {"_id": 1, "name": "Jenny"};
      sql = ` insert into ${TABLE} values (:1)`;
      result = await connection.execute(sql, [{
        type: oracledb.DB_TYPE_JSON,
        val: inpDocWithUserKey
      }]);
      sql = `select * from ${TABLE}`;
      result = await connection.execute(sql);
      genDoc = result.rows[2][0];
      assert.deepStrictEqual(genDoc, inpDocWithUserKey);
    }); // 244.12.1

  });  // 244.12

  describe('244.13 Read JSON data on meta data change', function() {

    const tableNameJSON = 'nodb_myjson_recreate';
    let sequence = 1;
    const sqlCreate = " CREATE TABLE " + tableNameJSON + " ( \n" +
    " id         NUMBER, \n" +
    " content    JSON \n" +
    " )";

    before('create table, insert data', async function() {
      if (!isRunnable) {
        this.skip();
      }
      await testsUtil.createTable(connection, tableNameJSON, sqlCreate);
    }); // before()

    after(async function() {
      if (!isRunnable) {
        this.skip();
      }
      oracledb.stmtCacheSize = default_stmtCacheSize;
      oracledb.fetchAsString = [];
      await testsUtil.dropTable(connection, tableNameJSON);
    }); // after()

    async function recreateTable() {
      await testsUtil.dropTable(connection, tableNameJSON);
      await testsUtil.createTable(connection, tableNameJSON, sqlCreate);
    }

    const testInsertAndFetch = async function(seq, jsonVal, resultStr, selectOpts) {
      let sql = "insert into " + tableNameJSON + " ( id, content ) values (:i, :c)";
      const binds = [
        { val: seq, type: oracledb.NUMBER, dir: oracledb.BIND_IN },
        { val: jsonVal, type: oracledb.DB_TYPE_JSON, dir: oracledb.BIND_IN }
      ];

      await connection.execute(sql, binds);
      sql = "select content as C from " + tableNameJSON + " where id = " + seq;
      const result = await connection.execute(sql, [], selectOpts);
      assert.strictEqual(result.rows[0][0], resultStr);
    };

    it('244.13.1 table recreate - with oracledb.fetchAsString', async function() {
      oracledb.fetchAsString = [ oracledb.DB_TYPE_JSON ];
      const jsonVals = [{ "key5": "2018/11/01 18:30:00" }];
      const resultStr = ["{\"key5\":\"2018/11/01 18:30:00\"}"];

      // Add the JSON Field with Long Field Name to the JSON Values Array
      // for Oracle DB 23.4 (and Oracle Client 23.4)
      if (isOracle_23_4) {
        const longFieldName = 'A'.repeat(1000);
        const jsonVal = {};
        jsonVal[longFieldName] = "2018/11/01 18:30:00";
        jsonVals.push(jsonVal);
        resultStr.push(`{"${longFieldName}":"2018/11/01 18:30:00"}`);
      }

      for (let i = 0; i < jsonVals.length; i++) {
        await testInsertAndFetch(sequence, jsonVals[i], resultStr[i], {});
        sequence++;
      }

      await recreateTable();
      sequence = 1;

      for (let i = 0; i < jsonVals.length; i++) {
        await testInsertAndFetch(sequence, jsonVals[i], resultStr[i], {});
        sequence++;
      }

    }); // 244.13.1

    it('244.13.2 table recreate - with fetchInfo oracledb.STRING', async function() {
      oracledb.fetchAsString = [];
      const jsonVal = { "key5": "2018/11/01 18:30:00" };
      const resultStr = "{\"key5\":\"2018/11/01 18:30:00\"}";

      const options = {
        fetchInfo: { C: { type: oracledb.STRING } }
      };

      // Test Insert and Fetch of JSON Data
      await testInsertAndFetch(sequence, jsonVal, resultStr, options);
      // Recreate the same table
      await recreateTable();
      // Test Insert and Fetch of JSON Data again
      await testInsertAndFetch(sequence, jsonVal, resultStr, options);
      sequence++;

    }); // 244.13.2

  }); // 244.13

});