ODP.NET 23.5 Now Available

ODP.NET 23ai was released two months ago, but that doesn’t mean we’re done adding new features. The Oracle .NET team is releasing a continuous stream of major and minor features every few months.

For the first ODP.NET 23ai production update (23.5), we’ve extended numerous usability and ease of use developer features. The ODP.NET 23.5 package downloads are available on NuGet Gallery.

All the features described are available in ODP.NET Core and managed ODP.NET only.

Ref Cursor Column Type

ODP.NET already has extensive Oracle Ref Cursor support. In the 23.5 release, ODP.NET adds the capability to return Ref Cursor type columns in a select list to an OracleDataReader, OracleRefCursor, or as JSON-formatted .NET string or OracleString.

This feature provides yet another way to consume Oracle Ref Cursors. It enhances flexibility for reading and managing result sets.

Two new ODP.NET methods allow you to retrieve these Ref Cursor column types:

• OracleDataReader.GetOracleDataReader
• OracleDataReader.GetOracleRefCursor

Existing ODP.NET methods can retrieve the Ref Cursor column types as JSON formatted strings, including

• OracleDataReader.GetString
• OracleDataReader.GetValue
• OracleDataReader.GetOracleString
• OracleDataReader.GetOracleValue

OpenTelemetry Activity Tag: Rows Affected

OpenTelemetry is a popular unified observability framework. In ODP.NET 23ai, OpenTelemetry tracing is built into the ODP.NET OpenTelemtery package.

ODP.NET OpenTelemetry 23.5 introduces a new trace attribute: SQL DML (INSERT, UPDATE, or DELETE) affected row count when using ODP.NET ExecuteNonQuery*, ExecuteReader*, or ExecuteScalar* methods. The trace results from this attribute are useful when monitoring database write operation impacts.

The attribute name is db.odp.rows_affected.

• ODP.NET OpenTelemetry

ODP.NET Product Info and Application Metadata

Besides OpenTelemetry, developers can incorporate their own custom app tracing using native ODP.NET and Oracle database properties. ODP.NET 23.5 includes new metadata properties to distinguish provider type (core or managed), NuGet version, and allow apps to assign a custom name to identify the client app in the database to make app tracing and debugging easier.

These new properties enable a straightforward way for both database administrators and application DevOps to identify the ODP.NET provider type, version, and application name. On the client side, ODP.NET stores the data in the following properties:

• OracleConnection.ProviderName: ODP.NET provider type and version
• OracleConnection.ProviderNuGetVersion: ODP.NET NuGet version
• OracleConfiguration.Program: string value app developers use to uniquely identify an app

On the database side, these property values are stored in the V$SESSION_CONNECT_INFO view under the CLIENT_DRIVER (ProviderName) and CLIENT_VERSION (ProviderNuGetVersion) columns and V$SESSION view under the PROGRAM column (Program).

Suppress GetDecimal Invalid Cast Exception Globally

Oracle NUMBER can have precision of up to 38 digits, while .NET Decimal is limited to up to 29. Large Oracle NUMBERs cast as Decimal types will produce invalid cast exceptions. When developers want to use a Decimal type, round off the NUMBER value implicitly, and suppress the exception message, they can set the SuppressGetDecimalInvalidCastException property to true on OracleDataReader or OracleDataAdapter in earlier ODP.NET releases.

In ODP.NET 23.5, the exception can be suppressed in more ways to cover usage scenarios flexibly, including

• Globally using OracleConfiguration.SuppressGetDecimalInvalidCastException
• At the connection level via OracleConnection.SuppressGetDecimalInvalidCastException
• For User-Defined Types (UDTs) when SuppressGetDecimalInvalidCastException is enabled on the configuration, connection, DataAdapter, or DataReader object
• For ODP.NET parameters when the property is enabled on the configuration or connection object

As you’ve likely surmised, SuppressGetDecimalInvalidCastException property on the OracleConfiguration and OracleConnection classes were added in the 23.5 release.

This feature improves ease of use and speed of development when working with large precision numbers. It eliminates the need to build extensive exception management or data conversion code.

Bulk Copy Notification After All Rows Processed

ODP.NET bulk copy enables apps to efficiently load large amounts of data into an Oracle table. Many times, the app wants to know when the bulk copy operation completes. ODP.NET 23.5 introduces a new member to the OracleBulkCopyOptions enumeration, NotifyAllRowsProcessed, that does just that.

If enabled, ODP.NET sends a notification after all bulk copy rows have been processed. The app can also retrieve the copied row count via the notification’s OracleRowsCopiedEventArgs.RowsCopied property.

By using this property, apps have an easy way to learn when their bulk copy operation completes and the total number of rows processed.

Oracle Cloud Infrastructure Object Storage Change Notification for ODP.NET Connection Information

In cloud deployments, ODP.NET caches connection string properties locally while the connection string of record is stored centrally on a cloud resource in an Oracle centralized configuration provider. Caching speeds up connection creation. If the property values change on the centralized configuration provider, cache updates can occur using a pull model (polling) or automated push model (change notification).

In ODP.NET 23.5, connection info stored in Oracle Cloud Infrastructure (OCI) Object Storage can now use change notification to keep apps updated.

Change notification is an automated way to receive fast updates for connection property changes without sacrificing the performance of storing the information locally. A big reason this update method is preferred is the source has the responsibility for updating the cache when its info has become invalid.

ODP.NET uses OCI Events Service and Notifications service to establish a communication channel for publishing messages using topics and subscriptions. When a change is detected, it sends a notification message to the app’s OCI event listener HTTPS endpoint. The listener receives the message. The app will then refresh the connection property values associated with the update notification upon their next use.

Using OCI change notification requires configuring OCI Object Storage, OCI Notifications service, event rules, an event listener, and ODP.NET. The ODP.NET documentation will guide you how to set up OCI Object Storage change notification.

• ODP.NET Connection URL Caching

Easy Connect Plus Address Lists

ODP.NET supports Easy Connect Plus, a simple URL-based method for Oracle database connection configuration. This new ODP.NET release incorporates address list support in Easy Connect Plus configurations.

With address lists, multiple lists of database addresses, each with its own characteristics can be configured for ODP.NET connections. This capability allows ODP.NET to be configured for more complex sets of database deployments.

• ODP.NET Easy Connect Plus Naming

Microsoft Identity Platform Tokens v2.0 Enhancement

ODP.NET supports Microsoft identity platform tokens v1.0 for Microsoft Entra ID-only applications and v2.0 for applications that support consumer accounts. ODP.NET 23.5 and higher check the roles (roles) and user principal name (UPN) claims in v2.0 tokens to verify at least one is present. If both claims are missing, the provider will report an error.

• ODP.NET Vault Wallets and Tokens

Conclusion

These features round out a robust initial ODP.NET 23ai release. The ODP.NET team plans to continue releasing new features every quarter to improve the Oracle .NET developer experience and capabilities.

Be sure to try out these new features. You can give us feedback on GitHub or ODP.NET discussion forum.