ODP.NET JSON Relational Duality and Oracle Database 23c Free
By now, you may have heard about a cool new Oracle Database feature called JSON Relational Duality. This feature is now available to all with Oracle Database 23c Free, which just released a developer version. As its name implies, this database is free to use. Plus, it includes many more breakthrough developer features.
In this blog post, I’ll focus on just one of these new database features, JSON Relational Duality, which .NET developers can start using today.
What is JSON Relational Duality?
JSON Relational Duality materializes in the Oracle database as fully updatable JSON database views over relational data. Data remains stored in relational tables in a highly efficient normalized format but can be accessed by any application in the form of JSON documents. The feature overcomes the historical challenge that developers face: having to choose either the relational model or the document model. .NET developers can now have the best of both worlds without the tradeoffs of either model. Most importantly, they only need to deal with one database and one data model, which provides great flexibility and simplicity.
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 version 19.
With version 21 and higher, ODP.NET can retrieve this JSON data as a .NET String or ODP.NET OracleString data type when parameters are bound with the OracleDbType.Json enumeration value.
ODP.NET 19c has an earlier implementation that doesn’t 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 19c apps should retrieve the data with OracleDataReader GetValue(s) or GetOracleValue(s) methods, but not GetString nor GetOracleString methods.
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 .NET string consisting of JSON. This makes developing with JSON Relational Duality much easier.
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 JSON data changes. Here’s a code example of how to perform these ODP.NET JSON updates and deletes:
// "JRDVIEW" is the JSON Relational Duality View being updated in the sample code
// 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;
// Execute
adapter.Update(ds);In the sample code, note 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, but it does provide a performance benefit. It allows the query to leverage indexes on the base tables. Other data types can be specified for similar performance improvement:
.string()for string types.date()for date data.timestamp()for timestamp data
Try JSON Relational Duality and Oracle Database 23c Free Today
You can get started now with Oracle Database 23c Free Developer Release via web download, yum, or container image.
One the client side, download ODP.NET from NuGet Gallery for the core and managed provider types or Oracle’s .NET website for the unmanaged provider type.
Learn more in the Oracle Database 23c Free documentation and JSON Relational Duality Developer’s Guide.
