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:
22.1 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 Database22.1.1 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.
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.
22.2 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 11322.3 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 useOCIJsonDomDocSet() 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.
22.3.1 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().
- Buffer Input –
oratext* - Streaming Input –
orastream *
22.3.2 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;
OCI_ATTR_JSON_DOM_MUTABLE to
FALSE in the JSON descriptor using
OCIAttrSet().
- Buffer Input –
oratext* - Streaming Input –
orastream *
22.4 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"
Figure 22-3 Calling Sequence for Reading JSON Data
Description of "Figure 22-3 Calling Sequence for Reading JSON Data"
22.5 JSON DOM Operations
This section describe the various JSON DOM operations.
22.5.1 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 |
|---|---|---|---|---|
|
|
|
|
oratext[n] |
JZNVAL_STRING |
|
|
|
|
OCINumber* |
JZNVAL_ORA_NUMBER |
|
|
Not Applicable |
Not Applicable | Not Applicable | JZNVAL_TRUE |
|
|
Not Applicable | Not Applicable | Not Applicable | JZNVAL_FALSE |
|
|
|
|
ub1[n] |
JZNVAL_BINARY |
|
|
|
|
double |
JZNVAL_DOUBLE |
|
|
BINARY_FLOAT |
SQLT_BFLOAT |
float |
JZNVAL_FLOAT |
|
|
|
|
JsonDateTime* |
JZNVAL_ORA_DATE |
|
|
|
|
JsonDateTime* |
JZNVAL_ORA_TIMESTAMP |
Timestamp with timezone |
TIMESTAMP WITH TIMEZONE |
SQLT_TIMESTAMP_TZ |
JsonDateTime* |
JZNVAL_ORA_TIMESTAMPTZ |
|
|
|
|
JsonDayInterval* |
JZNVAL_ORA_DAYSECOND_DUR |
|
|
|
|
JsonYearInterval* |
JZNVAL_ORA_YEARMONTH_DUR |
|
|
Not Applicable | Not Applicable | Not Applicable | JZNVAL_NULL |
See Also:
22.5.2 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;
#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 Datatype22.5.3 Building a JSON DOM
This section describes how to build a JSON DOM.
22.5.3.1 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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
JsonDomCreateBinary |
|
|
JsonDomCreateDouble |
|
|
|
|
|
|
|
|
|
JZNVAL_ORA_TIMESTAMPTZ |
JsonDomCreateOCIDateTime |
|
|
|
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 theJsonDomSetField() function to set the value of a specified
field to the specified object within DOM.
See Also:
JSON DOM Functions22.5.3.2 Building a DOM Using Scalar Nodes
This section describes how to build a DOM using various types of scalar nodes.
- 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 preservedKey: "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_NULLOCIJsonToTextBuffer() 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 Functions22.6 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.
22.7 Handling Character Sets
In OCI, the character set of textual input depends on the settings of OCI environment handle.
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:
IfJZN_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.