JSON Numeric Values

For managed ODP.NET and ODP.NET Core, JSON documents bound as OracleDbType.Json input parameters have a 28 precision upper limit for numeric values. In all other cases, up to 38 precision will be retained for JSON numeric values when sent to or retrieved from the database.

Unmanaged ODP.NET supports JSON numeric values with a maximum precision of 38, as does the database.

If a JSON document bound as OracleDbType.Json contains numeric values with higher precision than can be retained, then ODP.NET will round the value to the maximum supported precision. If more precision must be retained than the maximum, then store the numeric value as a string by placing double quotes around the JSON value before binding the JSON document as an input parameter.

JSON Relational Duality

JSON Relational Duality is a landmark capability introduced in Oracle Database that provides game-changing flexibility and simplicity for Oracle Database developers. This breakthrough innovation overcomes the historical challenges that developers faced when building applications, either when using the relational model or when using the document model.

JSON Relational Duality delivers a solution with the benefits of both relational tables and JSON documents, without the tradeoffs of either model.

In the database, JSON Relational Duality manifests as fully updatable JSON views over relational data. These views are called JSON Relational Duality Views.

ODP.NET and JSON Relational Duality Views

All ODP.NET provider types (core, managed, and unmanaged) support using JSON Relational Duality Views. ODP.NET supports the feature starting with release 19c.

With 21c and higher, ODP.NET can retrieve this JSON data as a .NET string or OracleString data type when parameters are bound with the OracleDbType.Json enumeration value.

ODP.NET 19c uses an earlier implementation that does not include this enumeration value. It fetches JSON data as a BLOB data type, then converts it to a string in .NET. With JSON Relational Duality, ODP.NET retrieves the data with OracleDataReader GetValue(s) or GetOracleValue(s) methods, but not GetString nor GetOracleString.

For the ODP.NET developer, the data type conversion and changes are transparent. No .NET data management needs to occur beyond treating the data as a string consisting of JSON in .NET.

Oracle JSON data can also be consumed and operated in .NET DataSet. When saving changes back to the Oracle Database from DataSet, the default generated OracleCommandBuilder insert operations will successfully complete. However, the default updates and deletes require some custom SQL with parameter binds to make these JSON data changes. Here's a code snippet of how to perform these JSON updates and deletes.

// “JRDVIEW” is the JSON Relational Duality View being updated in the example below
// Custom UPDATE SQL
string customUpdateSQL = @"UPDATE JRDVIEW SET data = :updatedJSON where json_value(data, '$.ID.number()') = json_value(:DBData, '$.ID.number()')";
adapter.UpdateCommand = new OracleCommand(customUpdateSQL, conn);
// Parameter bound to the updated data
adapter.UpdateCommand.Parameters.Add("updatedJSON", OracleDbType.Json, 100, "data"); 
// Parameter bound to row on DB
adapter.UpdateCommand.Parameters.Add("DBData", OracleDbType.Varchar2, 100, "data").SourceVersion = DataRowVersion.Original; 

// Custom DELETE SQL
string customDeleteSQL = @"DELETE FROM JRDVIEW WHERE json_value(data, '$.ID.number()') = json_value(:DBData, '$.ID.number()')";
adapter.DeleteCommand = new OracleCommand(customDeleteSQL, conn);
// Parameter bound to row on DB
adapter.DeleteCommand.Parameters.Add("DBData", OracleDbType.Varchar2, 100, "data").SourceVersion = DataRowVersion.Original; 

adapter.Update(ds);

In the sample code above, the ID field is a numeric type. We can specify it is a number by appending .number() to it in the SQL. Doing so is optional. However, the main benefit it provides is it allows the query to leverage indexes on the base tables, thereby improving execution performance. Other data types can be specified:

• .string() for string data types

• .date() for date data type

• .timestamp() for timestamp data type