OCI Support for JSON

22 OCI Support for JSON

Support for SQL type JSON is designed for JSON data. Oracle recommends you to use JSON type for your JSON data with Oracle Database.

This chapter contains these topics:

JSON Data Type Support

Starting Oracle Database Release 21c, support for SQL type JSON is designed specifically for JSON data. Oracle recommends that you use JSON type for your JSON data with Oracle Database. This uses a binary format, OSON, which is an optimized binary JSON format for Oracle used for fast query, and update in both Oracle database server and Oracle database clients. In order to use the JSON data type, the database initialization parameter, compatible must be set to at least 20.

When JSON data is of SQL data type JSON, then Oracle extends the set of standard JSON-language scalar types such as number, string, boolean, and null to include scalars that correspond to SQL scalar types such as binary, date, timestamp, year-month interval, day-second interval, double, and float. This enhances the JSON language, and makes conversion of the scalar data between that language and SQL more simple and with no loss in data.

See Also:

JSON Data and Oracle Database

OCI Representation for JSON

This section describes the OCI representation for JSON.

Standard JSON as a language or notation, has predefined data types such as object, array, number, string, boolean, and null. All JSON-language types except object, and array are scalar types.

OCIJson descriptor is used to represent a JSON document in OCI. It is identified by the descriptor type, OCI_DTYPE_JSON. The descriptor can be allocated and freed with the functions OCIDescriptorAlloc() and OCIDescriptorFree(). User can allocate and free an array of descriptors using OCIArrayDescriptorAlloc() and OCIArrayDescriptorFree() functions respectively.

A sample JSON document structure is as shown in the following figure:

Figure 22-1 Sample JSON Document

Description of "Figure 22-1 Sample JSON Document"

You can use OCIJsonDomDocGet() function to obtain a reference to the underlying JSON DOM (Document Object Model) container represented by JsonDomDoc *. A JSON DOM document contains nodes that are either object, array, or scalar types.

Table 22-1 Type Constructs and Constants

Program Variable	Type Constant
`JsonDomScalar`	`JZNDOM_SCALAR`
`JsonDomArray`	`JZNDOM_ARRAY`
`JsonDomObject`	`JZNDOM_OBJECT`

Note:

JsonDomNode is an incomplete type. The type values listed in the preceding table can be set to (JsonDomNode *) type.

Compatibility with Client Libraries Prior to Release 21c

This section describes how client versions prior to Oracle Database Release 21c can be made compatible for querying the JSON data type.

Starting with Oracle Database Release 21c, JSON data type is supported in both 21c version of RDBMS server and 21c version of the client libraries. This support enables the client programs to query JSON data and receive OSON bytes that can be natively decoded. This is recommended for better performance.

If you do not update your instant client version to 21c, your application can still read and write data to JSON type column as strings. So, to query data in a C OCI program, you can define using SQLT_CHR, SQLT_BLOB, or SQLT_CLOB data types. If the JSON data is extracted as BLOB data, then for both old and new clients, the JSON text is UTF-8 encoded. The result is in JSON text form. If SQLT_CHR is used as define type, then the JSON text size is restricted to 32K only. To overcome size limitation from SQLT_CHR, you must use SQLT_CLOB or SQLT_BLOB as define variable type.

The following code snippet shows how to query a JSON type column:

void testDescribe()
{
  oratext     stmt[400] = {0};
  ub4         stmtlen;
  ub4         numcols;
  ub4         i;
  OCIStmt    *stmthp;
  OCIParam *colhd = (OCIParam *) 0;
  OCIAuthInfo *authhp = (OCIAuthInfo *)0;
  sword status;

  getSession(&authhp);


  strcpy(stmt, "SELECT jcol FROM jtab where rownum < 2");
  stmtlen = strlen(stmt);

  Checkerr(errhp,
               OCIStmtPrepare2(svchp, &stmthp, errhp, stmt, stmtlen,
                               (oratext *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX,
                               (ub4) OCI_DEFAULT), "OCIStmtPrepare2");

  printf("\n########### Describe-only test  #############\n");

  Checkerr(errhp,
               OCIStmtExecute(svchp, stmthp, errhp, 0, 0,
                              (OCISnapshot *)0, (OCISnapshot *)0,
                              OCI_DESCRIBE_ONLY), "OCIStmtExecute");

  /* Get the number of columns in the query */
  Checkerr(errhp,
               OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, (void *)&numcols,
                           (ub4 *)0, OCI_ATTR_PARAM_COUNT, errhp), "OCIAttrGet");

  for (i = 1; i <= numcols; i++)
  {
    ub4 type = 0;

    Checkerr(errhp,
                 OCIParamGet((void *)stmthp, OCI_HTYPE_STMT, errhp,
                 (void **)&colhd, i), "OCIStmtParamGet");

    Checkerr(errhp,
                 OCIAttrGet((void*)colhd, OCI_DTYPE_PARAM, (void*)&type,
                            (ub4 *)0, OCI_ATTR_DATA_TYPE, errhp), "OCIAttrGet");

    printf("Col %d type %d\n", i, type);
  }

  releaseSession(authhp);
}

Following is the output of the preceding code showing that describe execution returns BLOB as the type of the JSON type column:

########### Describe-only test  #############
Col 1 type 113

See Also:

Mutable and Immutable DOM

This section describes the mutable and immutable forms of JSON DOM.

OCI descriptor can be used to capture one of the two forms of JSON DOM, mutable or immutable DOM. DOM nodes in the mutable DOM can be modified. Whereas, immutable DOM is read-only and any write operation on the DOM results in an error. The default DOM retained by the descriptor is immutable, unless it is altered using OCIAttrSet() function with the boolean attribute type.

Note:

You can use OCIJsonDomDocSet() function to set a JSON DOM container in the descriptor. The source DOM can be in a different form than descriptor form. That is, the source can be a mutable DOM and the target descriptor can be set to immutable DOM.

Manifesting JSON as a Mutable DOM

An OCI client application can manifest the JSON content as a mutable DOM. You can use XDK C DOM functions to read and write JSON elements such as objects, arrays, and scalars to or from a JSON document.

The following example code snippet shows how to set a DOM document reference to or from the OCIJson* descriptor in a mutable mode:

/* Set the JSON DOM MUTABLE Attribute to TRUE */
boolean attr = TRUE;
rc = OCIAttrSet(jsond,                  /* OCIJson descriptor */  
         OCI_DTYPE_JSON,                /* Descriptor type */ 
         &attr,                 
         OCI_ATTR_JSON_DOM_MUTABLE,     /* Attribute type */
         errhp);
if (rc != OCI_SUCCESS)  goto err_hndlr;

You can set the descriptor attribute OCI_ATTR_JSON_DOM_MUTABLE to TRUE using OCIAttrSet().

An OCI application can also set the following types of JSON inputs to the descriptor.

Buffer Input – oratext*
Streaming Input – orastream *

Manifesting JSON as an Immutable DOM

An OCI client application can manifest the JSON content as an immutable linear binary buffer backed by binary JSON.

The following example code snippet shows how to set a DOM document reference to or from the OCIJson* descriptor in a immutable mode. If this setting was not done for the descriptor, it defaults to value, FALSE:

/* Set the JSON DOM IMMUTABLE Attribute to FALSE */ 
boolean attr = FALSE; 
rc = OCIAttrSet(jsond,                        /* OCIJson descriptor */
                OCI_DTYPE_JSON,               /* Descriptor Type */                 
                &attr,          
                OCI_ATTR_JSON_DOM_MUTABLE,    /* Attribute type */                 
                errhp); 
if (rc != OCI_SUCCESS)  goto err_hndlr;

You can set OCI_ATTR_JSON_DOM_MUTABLE to FALSE in the JSON descriptor using OCIAttrSet().

An OCI application can also set the following types of JSON inputs to the descriptor.

Buffer Input – oratext*
Streaming Input – orastream *

Calling Sequence for Writing and Reading JSON Data

This section describes the calling sequence for writing and reading JSON data to the database.

The calling sequence for writing JSON data to the database is illustrated in the following figure:

Figure 22-2 Calling Sequence for Writing JSON Data

Description of "Figure 22-2 Calling Sequence for Writing JSON Data"

The calling sequence for reading JSON data to the database is illustrated in the following figure:

Figure 22-3 Calling Sequence for Reading JSON Data

Description of "Figure 22-3 Calling Sequence for Reading JSON Data"

JSON DOM Operations

This section describe the various JSON DOM operations.

Topics:

Scalar Types Mapping

This section lists and describes the scalar types mapping that are supported.

In textual JSON, only the standard JSON-language scalar types are supported. When JSON data is of SQL type JSON, then Oracle Database extends the set of standard JSON-language types to include several scalar types that correspond directly to SQL scalar data types such as binary, date, timestamp, double, float, year-month interval, day-second interval.

Analogous to this, JSON values fetched from the database with OCIJson as the program variable have direct mapping to the extended scalar types. These primitive type mappings are summarized in the following table:

Table 22-2 Scalar Types Mapping

JSON Scalar Type	Database SQL Type	OCI SQL Type Constant	OCI Program Variable	JSON Scalar Type Constant
`String`	`VARCHAR2`	`SQLT_CHR`	`oratext[n]`	`JZNVAL_STRING`
`Number`	`NUMBER`	`SQLT_VNU`	`OCINumber*`	`JZNVAL_ORA_NUMBER`
`True (Boolean)`	Not Applicable	Not Applicable	Not Applicable	`JZNVAL_TRUE`
`False (Boolean)`	Not Applicable	Not Applicable	Not Applicable	`JZNVAL_FALSE`
`Binary`	`RAW`	`SQLT_BIN`	`ub1[n]`	`JZNVAL_BINARY`
`Double`	`BINARY_DOUBLE`	`SQLT_BDOUBLE`	`double`	`JZNVAL_DOUBLE`
`Float`	`BINARY_FLOAT`	`SQLT_BFLOAT`	`float`	`JZNVAL_FLOAT`
`Date`	`DATE`	`SQLT_ODT`	`JsonDateTime*`	`JZNVAL_ORA_DATE`
`Timestamp`	`TIMESTAMP`	`SQLT_TIMESTAMP`	`JsonDateTime*`	`JZNVAL_ORA_TIMESTAMP`
`Timestamp with timezone`	`TIMESTAMP WITH TIMEZONE`	`SQLT_TIMESTAMP_TZ`	`JsonDateTime*`	`JZNVAL_ORA_TIMESTAMPTZ`
`Day-Second Interval`	`INTERVAL DAY TO SECOND`	`SQLT_INTERVAL_DS`	`JsonDayInterval*`	`JZNVAL_ORA_DAYSECOND_DUR`
`Year-Month Interval`	`INTERVAL YEAR TO MONTH`	`SQLT_INTERVAL_YM`	`JsonYearInterval*`	`JZNVAL_ORA_YEARMONTH_DUR`
`Null`	Not Applicable	Not Applicable	Not Applicable	`JZNVAL_NULL`

See Also:

Reading JSON DOM Scalar Nodes

This section describes how to read JSON DOM scalar nodes.

OCI implicitly converts binary float and double to IEEE float and double formats. For number, timestamp, day-second interval, and year-month interval types, you can use the type, JsonOCIVal * to read the data in a structured form using the JsonDomGetScalarInfoOci() function. JsonOCIVal * is a C union defined as shown in the following code snippet:

/* Auxiliary Union of helper structures */
typedef union JsonOCIVal
{
  JsonDateTime      dt_JsonOCIVal;
  JsonDayInterval   dayInv_JsonOCIVal;
  JsonYearInterval  yrInv_JsonOCIVal;
  ub1               num_JsonOCIVal[JZN_ORA_NUM_MAX_LEN];
} JsonOCIVal;

The following code snippet shows how to fetch the values from a scalar node from the JSON DOM:


#include <ocijson.h>
void introspectDomNode(appctx         *c,
                       JsonDomDoc     *doc,
                       JsonDomNode    *node)
{
  JsonOCIVal     av;
  jznScalarVal   sval;
  jznnodetype    ntype;
  /* Check the JSON node type */
  ntype =  JsonDomGetNodeType(doc, node);
  if (ntype == JZNDOM_SCALAR)
  {
    /* Get information for this JSON scalar node */
    JsonDomGetScalarInfoOci(doc, (JsonDomScalar *)node, &sval, &av);
    printScalarInfo(c, &sval, &av);
  }
  else if (ntype == JZNDOM_ARRAY) {...}
  else if (ntype == JZNDOM_OBJECT) {...}
  ...
}
void printScalarInfo(appctx          *c, 
                     jznScalarVal    *sval,
                     JsonOCIVal      *av)
{
  jznvaltype       vtype = sval->type_jznScalarVal;
  ub4              i;
  switch (vtype)
  {
    case JZNVAL_STRING:
      printf("Type: JZNVAL_STRING\n");
      printf("Value: %.*s\n", sval->len_jznScalarVal, sval->val_jznScalarVal);
    break;
    case JZNVAL_BINARY:
      printf("Type: JZNVAL_BINARY\n");
      printf("Value: ");
      for (i = 0 ; i < sval->binlen_jznScalarVal; i++)
        printf("%X", (sval->binval_jznScalarVal)[i]);
      printf("\n");
    break;
    case JZNVAL_FLOAT:
      printf("Type: JZNVAL_FLOAT\n");
      printf("Value: %f\n", sval->flt_jznScalarVal);
    break;
    case JZNVAL_DOUBLE:
      printf("Type: JZNVAL_DOUBLE\n");
      printf("Value: %lf\n", sval->db_jznScalarVal);
    break;
    case JZNVAL_TRUE:
      printf("Type: JZNVAL_TRUE\n");
    break;
    case JZNVAL_FALSE:
      printf("Type: JZNVAL_FALSE\n");
    break;
    case JZNVAL_NULL:
      printf("Type: JZNVAL_NULL\n");
    break;
    case JZNVAL_ORA_NUMBER:
    {
      double  nval;
      printf("Type: JZNVAL_ORA_NUMBER\n");
      OCINumberToReal(c->errhp, (const OCINumber *) av, (uword) sizeof(nval), &nval);          
      printf("Value: %d\n", nval);
      break;
    }
    case JZNVAL_ORA_DATE:
    {
      JsonDateTime  *ts;
      printf("Type: JZNVAL_ORA_DATE\n");
      ts = &(av->dt_JsonOCIVal);
      printf("Value: %d-%d-%d\n", ts->year_JsonDateTime, ts->month_JsonDateTime,
             ts->day_JsonDateTime);
      break;
    }
    case JZNVAL_ORA_TIMESTAMP:
    {
      JsonDateTime  *ts;
      printf("Type: JZNVAL_ORA_TIMESTAMP\n");
      ts = &(av->dt_JsonOCIVal);
      printf("Value: %d-%d-%d %d:%d:%d:%d\n", ts->year_JsonDateTime,
             ts->month_JsonDateTime, ts->day_JsonDateTime, ts->hour_JsonDateTime,
             ts->minute_JsonDateTime, ts->second_JsonDateTime,
             ts->fsecond_JsonDateTime);
      break;
    }
    case JZNVAL_ORA_TIMESTAMPTZ:
    {
      JsonDateTime  *ts;
      printf("Type: JZNVAL_ORA_TIMESTAMPTZ\n");
      ts = &(av->dt_JsonOCIVal);
      printf("Value: %d-%d-%d %d:%d:%d:%d %03d:%02d\n", ts->year_JsonDateTime,
             ts->month_JsonDateTime, ts->day_JsonDateTime, ts->hour_JsonDateTime,
             ts->minute_JsonDateTime, ts->second_JsonDateTime,
             ts->fsecond_JsonDateTime, ts->tzHourOffset_JsonDateTime,
             ts->tzMinuteOffset_JsonDateTime);
      break;
    }

    case JZNVAL_ORA_YEARMONTH_DUR:
    {
      JsonYearInterval  *yint;
      printf("Type: JZNVAL_ORA_YEARMONTH_DUR\n");
      yint = &(av->yrInv_JsonOCIVal);
      printf("Value: %dY-%dM\n",
             yint->years_JsonYearInterval, yint->months_JsonYearInterval);
      break;
    }
    case JZNVAL_ORA_DAYSECOND_DUR:
    {
      JsonDayInterval *dint;
      printf("Type: JZNVAL_ORA_DAYSECOND_DUR\n");
      dint = &(av->dayInv_JsonOCIVal);
      printf("Value: %dD-%dH-%dM-%dS-%dSS\n",
             dint->days_JsonDayInterval, dint->hours_JsonDayInterval,
             dint->minutes_JsonDayInterval, dint->seconds_JsonDayInterval,
             dint->fseconds_JsonDayInterval);
      break;
    }
    default:
      printf("ERROR: Unsupported value type encountered [%d]\n", vtype);
    break;
  }
}

See Also:

jznScalarVal Datatype

Building a JSON DOM

This section describes how to build a JSON DOM.

Topics:

JSON Scalar Types and Scalar Constructors

This section lists the JSON scalar types with corresponding scalar constructor.

In a mutable DOM, new nodes can be added and existing nodes can be modified. The following table summarizes the scalar types and its corresponding constructor function.

Table 22-3 Scalar Types and Contructors

JSON Scalar Type	Scalar Constructor
`JZNVAL_STRING`	`JsonDomCreateString`
`JZNVAL_ORA_NUMBER`	`JsonDomCreateOCINumber`
`JZNVAL_TRUE`	`JsonDomCreateBoolean`
`JZNVAL_FALSE`	`JsonDomCreateBoolean`
`JZNVAL_BINARY`	`JsonDomCreateBinary`
`JZNVAL_DOUBLE`	`JsonDomCreateDouble`
`JZNVAL_FLOAT`	`JsonDomCreateFloat`
`JZNVAL_ORA_DATE`	`JsonDomCreateOCIDate`
`JZNVAL_ORA_TIMESTAMP`	`JsonDomCreateOCIDateTime`
`JZNVAL_ORA_TIMESTAMPTZ`	`JsonDomCreateOCIDateTime`
`JZNVAL_NULL`	`JsonDomCreateNull`
`JZNVAL_ORA_YEARMONTH_DUR`	`JsonDomCreateOCIInteval`
`JZNVAL_ORA_DAYSECOND_DUR`	`JsonDomCreateOCIInteval`

Constructor functions for non-scalar types

The following table summarizes the constructor functions for non-scalar types (array and object).

JSON Node Type	Scalar Constructor
`JZNDOM_ARRAY`	`JsonDomCreateArray`
`JZNDOM_OBJECT`	`JsonDomCreateObject`

Note:

You can use the JsonDomSetField() function to set the value of a specified field to the specified object within DOM.

See Also:

JSON DOM Functions

Building a DOM Using Scalar Nodes

This section describes how to build a DOM using various types of scalar nodes.

The following code shows how to build a DOM using various types of scalar nodes and this code performs the following operations:

Allocates a JSON descriptor and sets the mutable property
Creates scalar nodes of different types (string, number, boolean, binary, double, float, date, timestamp, null) and adds these nodes to the DOM
Sets the JSON DOM container in the descriptor
Serializes the JSON descriptor to the text
Frees the descriptors


#include <ocijson.h>
 
sword buildDom(appctx          *c,
               JsonDomDoc      *jdoc,
               JsonDomObject   *root,
               boolean          ismut)
{
 
  OCIJson        *jsond;
  oratext         outbuf[1024] = {0};
  oraub8          outlen       = 1024;
  JsonDomScalar  *node;
 
  /* Field names */
  oratext        *s_name     = (oratext *) "string_val";
  oratext        *n_name     = (oratext *) "number_val";
  oratext        *bt_name    = (oratext *) "true_val";
  oratext        *bf_name    = (oratext *) "false_val";
  oratext        *b_name     = (oratext *) "binary_val";
  oratext        *d_name     = (oratext *) "double_val";
  oratext        *f_name     = (oratext *) "float_val";
  oratext        *dt_name    = (oratext *) "date_val";
  oratext        *dtt_name   = (oratext *) "datetime_val";
  oratext        *dttz_name  = (oratext *) "datetimetz_val";
  oratext        *yminv_name = (oratext *) "yminterval_val";
  oratext        *dsinv_name = (oratext *) "dsinterval_val";
  oratext        *nl_name    = (oratext *) "null_val";
 
  /* Values */
  oratext        *sval    = (oratext *) "Strings are sequence of characters";
  ub4             slen    = (ub4) strlen(sval);
  int             inval   = -29873546;
  OCINumber       nval;
  boolean         btval   = TRUE;
  boolean         bfval   = FALSE;
  ub1             bval[8] = {0x000D, 0x000E, 0x000A, 0x000D,
                             0x000B, 0x000E, 0x000E, 0x000F};
  ub4             blen    = (ub4) 8;
  double          dval    = 34837749.5699837;
  float           fval    = -133424.75;
  OCIDate        *odval   = NULL;
  sb2             yrval   = 2020;
  ub1             mnval   = 10;
  ub1             dyval   = 25;
  OCIDateTime    *odtval  = NULL;
  ub1             hrval   = 8;
  ub1             minval  = 32;
  ub1             secval  = 56;
  ub4             fsecval = 123456789;
  OCIDateTime    *odtzval = NULL;
  oratext        *tzone   = (oratext *)"-05:30";
  ub4             tzlen   = (ub4) strlen(tzone);
  OCIInterval    *yminval = NULL;
  oratext        *yminvt  = (oratext *)"04-11";
  OCIInterval    *dsinval = NULL;
  oratext        *dsinvt  = (oratext *)"11 10:36:19.000005";
 
 
  /* (1) Allocate JSON descriptor and set mutable property */
  checkerr("Allocate JSON descriptor", c,
            OCIDescriptorAlloc(c->envhp, (void **) &jsond,
                                OCI_DTYPE_JSON, 0, 0));
  checkerr("Attr set mutable", c,
            OCIAttrSet((void *) jsond, OCI_DTYPE_JSON, &ismut, 0,
                        OCI_ATTR_JSON_DOM_MUTABLE, c->errhp));
 
  /* (2) Create scalar fields and add to DOM */
  /* (a) Add string field */
  node = JsonDomCreateString(jdoc, sval, slen);
  JsonDomSetField(jdoc, root, s_name, (ub2) strlen(s_name),
                  (JsonDomNode *) node);
 
  /* (b) Add number field */
  checkerr("Create OCINumber", c,
           OCINumberFromInt(c->errhp, &inval, sizeof(int), OCI_NUMBER_SIGNED,
                            &nval));
  node = JsonDomCreateOCINumber(jdoc, &nval);
  JsonDomSetField(jdoc, root, n_name, (ub2) strlen(n_name),
                  (JsonDomNode *) node);
 
  /* (c) Add boolean TRUE field */
  node = JsonDomCreateBoolean(jdoc, btval);
  JsonDomSetField(jdoc, root, bt_name, (ub2) strlen(bt_name),
                  (JsonDomNode *) node);
 
  /* (d) Add boolean FALSE field */
  node = JsonDomCreateBoolean(jdoc, bfval);
  JsonDomSetField(jdoc, root, bf_name, (ub2) strlen(bf_name),
                  (JsonDomNode *) node);
 
  /* (e) Add binary field */
  node = JsonDomCreateBinary(jdoc, bval, blen);
  JsonDomSetField(jdoc, root, b_name, (ub2) strlen(b_name),
                  (JsonDomNode *) node);
 
  /* (f) Add double field */
  node = JsonDomCreateDouble(jdoc, dval);
  JsonDomSetField(jdoc, root, d_name, (ub2) strlen(d_name),
                  (JsonDomNode *) node);
 
  /* (g) Add float field */
  node = JsonDomCreateFloat(jdoc, fval);
  JsonDomSetField(jdoc, root, f_name, (ub2) strlen(f_name),
                  (JsonDomNode *) node);
 
  /* (h) Add date field */
  checkerr("Create OCIDate", c,
           OCIDescriptorAlloc(c->envhp, (void **) &odval, OCI_DTYPE_DATE,
                              0, NULL));
  OCIDateSetDate(odval, yrval, mnval, dyval);
  node = JsonDomCreateOCIDate(jdoc, odval);
  JsonDomSetField(jdoc, root, dt_name, (ub2) strlen(dt_name),
                  (JsonDomNode *) node);
 
  /* (i) Add datetime field */
  checkerr("Create OCIDateTime", c,
           OCIDescriptorAlloc(c->envhp, (void **) &odtval, OCI_DTYPE_TIMESTAMP,
                              0, NULL));
  checkerr("Construct OCIDateTime timezone", c,
           OCIDateTimeConstruct (c->envhp, c->errhp, odtval,
                                 yrval-10, mnval-5, dyval-10,
                                 hrval, minval, secval, fsecval, NULL, 0));
  node = JsonDomCreateOCIDateTime(jdoc, odtval);
  JsonDomSetField(jdoc, root, dtt_name, (ub2) strlen(dtt_name),
                  (JsonDomNode *) node);
 
  /* (j) Add datetime timezone field */
  checkerr("Create OCIDateTime timezone", c,
           OCIDescriptorAlloc(c->envhp, (void **) &odtzval, OCI_DTYPE_TIMESTAMP_TZ,
                              0, NULL));
  checkerr("Construct OCIDateTime timezone", c,
           OCIDateTimeConstruct (c->envhp, c->errhp, odtzval,
                        yrval-7, mnval-3, dyval-2,
                        hrval-1, minval-10, secval-25, fsecval-98765432,
                        tzone, tzlen));
  node = JsonDomCreateOCIDateTime(jdoc, odtzval);
  JsonDomSetField(jdoc, root, dttz_name, (ub2) strlen(dttz_name),
                  (JsonDomNode *) node);
 
  /* (k) Add year-month interval field */
  checkerr("Create OCIInterval year-month", c,
           OCIDescriptorAlloc(c->envhp, (void **) &yminval, OCI_DTYPE_INTERVAL_YM,
                              0, NULL));
  checkerr("Construct OCIInterval year-month", c,
           OCIIntervalFromText(c->envhp, c->errhp, yminvt, strlen(yminvt), yminval));
  node = JsonDomCreateOCIInterval(jdoc, yminval);
  JsonDomSetField(jdoc, root, yminv_name, (ub2) strlen(yminv_name),
                  (JsonDomNode *) node);
 
  /* (l) Add day-second interval field */
  checkerr("Create OCIInterval day-second", c,
           OCIDescriptorAlloc(c->envhp, (void **) &dsinval, OCI_DTYPE_INTERVAL_DS,
                              0, NULL));
  checkerr("Construct OCIInterval day-second", c,
           OCIIntervalFromText(c->envhp, c->errhp, dsinvt, strlen(dsinvt), dsinval));
  node = JsonDomCreateOCIInterval(jdoc, dsinval);
  JsonDomSetField(jdoc, root, dsinv_name, (ub2) strlen(dsinv_name),
                  (JsonDomNode *) node);
 
  /* (m) Add NULL field */
  node = JsonDomCreateNull(jdoc);
  JsonDomSetField(jdoc, root, nl_name, (ub2) strlen(nl_name),
                  (JsonDomNode *) node);
 
  /* (3) Set the JSON DOM container in the descriptor */
  checkerr("Set JSON DOM container", c,
            OCIJsonDomDocSet(c->svchp, jsond, jdoc, c->errhp, 0));
 
  /* (4) Serialize JSON descriptor to text */
  checkerr("To Text Buffer", c,
            OCIJsonToTextBuffer(c->svchp, jsond, outbuf, &outlen,
                                JZNU_PRINT_PRETTY, c->errhp,
                                OCI_JSON_TEXT_ENV_NLS));
  printf("Descriptor content:\n");
  printf("%.*s \n", outlen, outbuf);
 
finally:
 
  /* (5) Free the descriptors */
  if (odval)
    checkerr("Free Date descriptor", c,
              OCIDescriptorFree(odval, OCI_DTYPE_DATE));
  if (odtval)
    checkerr("Free DateTime descriptor", c,
              OCIDescriptorFree(odtval, OCI_DTYPE_TIMESTAMP));
  if (odtzval)
    checkerr("Free DateTime timezone descriptor", c,
              OCIDescriptorFree(odtzval, OCI_DTYPE_TIMESTAMP_TZ));
  if (yminval)
    checkerr("Free Interval year-month descriptor", c,
              OCIDescriptorFree(yminval, OCI_DTYPE_INTERVAL_YM));
  if (dsinval)
    checkerr("Free Interval day-second descriptor", c,
              OCIDescriptorFree(dsinval, OCI_DTYPE_INTERVAL_DS));
  if (jsond)
    checkerr("Free JSON descriptor", c,
              OCIDescriptorFree(jsond, OCI_DTYPE_JSON));
 
  return c->status;
}

The function printScalarInfo() returns the following output:

Note:

All the types information are preserved

Key: "string_val"
Type: JZNVAL_STRING
Value: Strings are sequence of characters

Key: "number_val"
Type: JZNVAL_ORA_NUMBER
Value: -29873546.000000

Key: "true_val"
Type: JZNVAL_TRUE

Key: "false_val"
Type: JZNVAL_FALSE

Key: "binary_val"
Type: JZNVAL_BINARY
Value: DEADBEEF

Key: "double_val"
Type: JZNVAL_DOUBLE
Value: 34837749.569984

Key: "float_val"
Type: JZNVAL_FLOAT
Value: -133424.750000

Key: "date_val"
Type: JZNVAL_ORA_DATE
Value: 2020-10-25

Key: "datetime_val"
Type: JZNVAL_ORA_TIMESTAMP
Value: 2010-5-15 8:32:56:123456789

Key: "datetimetz_val"
Type: JZNVAL_ORA_TIMESTAMPTZ
Value: 2013-7-23 7:22:31:24691357 -05:-30

Key: "yminterval_val"
Type: JZNVAL_ORA_YEARMONTH_DUR
Value: 4Y-11M

Key: "dsinterval_val"
Type: JZNVAL_ORA_DAYSECOND_DUR
Value: 11D-10H-36M-19S-5000SS

Key: "null_val"
Type: JZNVAL_NULL

The OCIJsonToTextBuffer() function returns the following textual JSON output:

Note:

The extended types are lost and are converted into strings

{
  "true_val" : true,
  "number_val" : -29873546,
  "string_val" : "Strings are sequence of characters",
  "date_val" : "2020-10-25T00:00:00",
  "dsinterval_val" : "P11DT10H36M19.000005S",
  "yminterval_val" : "P4Y11M",
  "datetime_val" : "2010-05-15T08:32:56.123456789",
  "binary_val" : "0D0E0A0D0B0E0E0F",
  "null_val" : null,
  "false_val" : false,
  "datetimetz_val" : "2013-07-23T07:22:31.024691357-05:30",
  "float_val" : -133424.75,
  "double_val" : 34837749.5699837
}

See Also:

JSON DOM Functions

Multithreading Using JSON Descriptor

OCIJson* descriptor is not thread-safe. It is the responsibility of the user to ensure that a descriptor and its descendant DOM nodes are manipulated with only one thread at a time.

Handling Character Sets

In OCI, the character set of textual input depends on the settings of OCI environment handle.

If the user does not specify the csid parameter when creating OCIEnv*, then the NLS_LANG settings are used as default settings in the handle. For APIs, such as OCIJsonTextBufferParse(), OCIJsonTextStreamParse() where textual JSON is the input, the input can be in any Oracle recognized character set, (as long as it conforms to JSON syntax) and not necessarily the one set in the environment handle or NLS_LANG parameter.

Note:

If JZN_INPUT_DETECT is used for unicode-encoded inputs, the encoding of the input is detected as one of UTF-8, UTF-16 (LE or BE) and is processed accordingly. If the textual JSON input is not in one of the unicode encodings in this mode, then it is an user error and the behavior is not guaranteed.

OCIJson APIs, such as OCIJsonToTextBuffer (), OCIJsonToTextStream () return textual JSON in AL32UTF8 character set, unless OCI_JSON_TEXT_ENV_NLS mode is set.