Introducing ODP.NET 21c: EF Core 5, JSON, and More

I’m happy to announce ODP.NET 21c is now available. You can download ODP.NET Core and Oracle Entity Framework Core from NuGet Gallery. New features include:

• Entity Framework Core 5
• JSON column data type
• Client Initiated Continuous Query Notification (CICQN)
• Sharding
• More administrative privileges
• Debug tracing redaction

Let’s dive into each of these new features.

Entity Framework Core 5

ODP.NET Core 21c now supports Microsoft Entity Framework (EF) Core 5. EF Core 5 is the latest Microsoft object-relational mapper version.

For most applications, upgrading from the Oracle EF Core 3.1 provider to the Oracle EF Core 5 provider does not require code changes. If you do call the UseOracleIdentityColumn and ForOracleUseSequenceHiLo methods, now call the UseIdentityColumn and UseHiLo methods, respectively, in EF Core 5.

Oracle EF Core 5 supports new EF Core 5 APIs. Most of these APIs follow the standard behavior described in the Microsoft EF Core documentation. There can be some differences. For example, the IQueryingEnumerable ToQueryString extension method typically provides the SQL text for the Oracle query. The unmodified SQL can be used with database server versions 12.1 and higher. For 11.2.0.4 , some documented minor modifications are required to run the generated SQL against this older database version.

For more information about Oracle EF Core 5, review the documentation included in the ODP.NET EF Core 5 NuGet package or the online ODP.NET 21c documentation. You will find the PDF when you unzip the package.

JSON

ODP.NET supports the native JavaScript Object Notation (JSON) column data type in Oracle Database 21c. The new binary JSON data type is optimized for query and DML processing, yielding performance improvements. This new native JSON binary format is often to referred to as OSON.

Oracle Database or ODP.NET will implicitly perform the decoding and encoding between OSON and the client data type. Developers do not even need to be aware the database is using OSON. When the JSON data type is retrieved or passed to the database using a .NET string or OracleString and bound as a parameter using the OracleDbType.Json enumeration value, ODP.NET performs the data type conversion. In all other cases when the JSON data type is retrieved from or returned to the database, the database itself performs the type conversion instead of ODP.NET.

ODP.NET Core, managed, and unmanaged all support these new JSON features.

This ODP.NET Core JSON code sample demonstrates retrieving a native Oracle Database JSON type to a .NET DataSet. If you don’t have an Oracle Database 21c set up, you can create a free Oracle Autonomous Database 21c in minutes.

From the code sample, observe there are no new ODP.NET APIs needed when using the new JSON type. Using this new database type is transparent to the developer.

Client Initiated Continuous Query Notification

Client Initiated Continuous Query Notification (CICQN) is similar to the traditional Continuous Query Notification (CQN) feature available already in ODP.NET. In CQN, client applications receive a notification when a server side change that would affect the client’s query result set, the underlying schema objects, or the database state occurs. This notification is out of process, occurring without needing an ODP.NET connection.

The new CICQN feature uses in-process notifications. ODP.NET creates one separate connection per pool for receiving notifications. The app does not need to manage this connection. ODP.NET handles it automatically. The database server uses this ODP.NET-owned connection to send change notifications to the app. CICQN is useful when out of process communications is not available between client and database server, such as cloud deployments or when firewalls exist between client and server.

ODP.NET 21c introduces support for CICQN in the core, managed and unmanaged providers. CICQN’s dedicated connection supplants the traditional CQN listening endpoint. This connection aggregates all the database change notifications for the pool’s users. ODP.NET attempts to always keep this connection open. It does not count toward Min Pool Size and Max Pool Size limits.

ODP.NET CICQN requires Oracle Database 21c or higher. It can be enabled by setting the OracleConfiguration UseClientInitiatedCQN static Boolean property or .NET Framework configuration file UseClientInitiatedCQN setting to true. By default, it is false.

This ODP.NET Core CICQN code sample demonstrates how to enable CICQN and use change notifications with an event handler. As mentioned previously, if you don’t have an Oracle Database 21c set up, you can create a free Oracle Autonomous Database 21c in minutes.

Sharding

ODP.NET Core and managed providers now support sharding. Oracle Sharding provides the ability to horizontally partition data across multiple independent Oracle databases (shards). Based on a key specified in the connection string, ODP.NET routes database requests to a particular shard.

Oracle Sharding is a shared-nothing architecture that allows near-linear scaling of the database across low-cost commodity database servers located in one or more local or global data centers. Other key benefits include global data distribution (store particular data close to consumers) and fault containment (failure of one shard does not affect the availability of other shards). Global Data Services manages the location of data among the shards and allows ODP.NET client requests to be routed to the appropriate shard in this distributed database system.

ODP.NET 21c managed and core providers now support the same sharding functionality as unmanaged ODP.NET does. That functionality includes using sharding keys and super sharding keys to quickly locate where a specific row resides. In addition, managed and core providers support pausing connection requests during chunk migrations. During such a migration, users will not experience a timeout without giving the chunk sufficient time to migrate across shards.

More Administrative Privileges

ODP.NET 21c connections now support assignment of more task-specific administrative privileges to enable database administrator duty separation. The newly added privileges include:

• SYSBACKUP for backup and recovery
• SYSDG for Oracle Data Guard
• SYSKM for encryption key management
• SYSRAC for Oracle Real Applications Clusters operations.

These privileges can be set on the DBA Privilege connection string attribute. They are available in addition to the existing SYSASM, SYSDBA, and SYSOPER attributes. Core, managed, and unmanaged ODP.NET support all these administrative privileges.

Trace Redaction

ODP.NET 21c for the managed and Core providers have introduced a new trace level that excludes SQL statements and network packet contents from being written to the trace. This new trace level’s value is 8 and can be set in the TraceLevel property in a bitwise OR manner.

What’s Next — User Defined Types

Looking ahead to the second ODP.NET 21c release, the core and managed providers will add user-defined types (UDT) support. Oracle Collections (VARRAY and nested tables), Oracle Objects, and references (REF) to object types will be among the supported types. These new features provide near-parity with existing ODP.NET, Unmanaged Driver UDT functionality. The vast majority of unmanaged ODP.NET UDT applications will be able to migrate to managed ODP.NET or ODP.NET Core easily with minimal code changes.

UDT support is just one of the features we have planned for the second ODP.NET 21c release.

Conclusion

ODP.NET 21c lets you use the latest new Oracle Database 21c features, such as JSON column data type and CICQN, new .NET features, such as EF Core 5, and enables more database capabilities than ever before.

For existing databases, ODP.NET 21c is backwards compatible with Oracle Database server 11.2.0.4 and higher. It works with both on-premises and cloud databases, including all Oracle Autonomous Database versions. Users can upgrade to ODP.NET 21c and still remain with their current supported database server version.

This is just the beginning for ODP.NET 21c. More features, managed ODP.NET, and unmanaged ODP.NET will be arriving soon!

More about Oracle Database 21c

Oracle Autonomous Database is not the only way to access Oracle Database 21c today. As explained in the Oracle Database blog:

“Oracle Database 21c is now generally available “cloud first” in the Oracle Cloud Database Service Virtual Machine (for RAC and single instance) and Bare Metal Service (single instance). It’s also available in the Autonomous Database Free Tier Service in Ashburn (IAD), Phoenix (PHX), Frankfurt (FRA) and London (LHR) regions. General availability of Oracle Database 21c for on-prem platforms (including Exadata, Linux and Windows) will follow along in 2021.

If you are interested in Oracle Database 21c, the blog post provides a great summary of the major new features.