Announcing ODP.NET 21.3 — User-Defined Types for Managed ODP.NET and ODP.NET Core

I’m excited to announce the second ODP.NET 21c release, version 21.3, is now available on NuGet Gallery for both managed ODP.NET and ODP.NET Core. Unmanaged ODP.NET 21.3.

This is the first managed ODP.NET 21c release, bringing the many new Oracle database capabilities to .NET Framework developers. Those new features include a binary JSON data type, Client Initiated Continuous Query Notification, sharding, and more. These new ODP.NET 21c features were described in a previous blog post if you’d like to know more. For this blog post, I will focus on the major new User-Defined Type (UDT) feature that many developers have been asking for. UDTs are commonly used in object-relational applications.

ODP.NET UDTs

Oracle Database UDTs allow developers to work with complex sets of data more easily. Business data can inhabit a more natural form. The database and .NET apps can then manage the entity as a unit, which improves productivity and efficiency. Many different Oracle Database components use UDTs inherently, such as Oracle Spatial and XML DB, as well as innumerable custom types customers create to work with their business data entities.

ODP.NET supports two Oracle UDT types:

• Object types, such as Oracle Objects
• Collection types, such as VARRAY types and nested table types.

Additionally, ODP.NET supports references (REF) to object types.

Historically, unmanaged ODP.NET support UDTs. Just like unmanaged ODP.NET, managed ODP.NET and ODP.NET Core apps represent Oracle Database UDTs as .NET custom types. Apps must apply .NET attributes to custom classes and structs, and to their public fields and properties. This same process exists with managed ODP.NET and ODP.NET Core.

I’ve published several ODP.NET UDT code samples on GitHub to help managed ODP.NET and ODP.NET Core developers get started. These samples cover common usage scenarios:

• Object-UDT.cs: Map, fetch, and manipulate an UDT a .NET custom object
• Spatial-UDT.cs: Map and fetch Oracle Spatial types as custom types
• VArray.cs: Map, fetch, and manipulate a VARRAY as a custom object
• Nested-Table.cs: Map, fetch, and manipulate a nested table of UDTs that has an inheritance hierarchy (i.e. parent and child types)
• Ref.cs: Fetch UDTs referenced by REFs
• Ref-Inheritance.cs: Obtain custom type objects from OracleRef objects, update UDTs through the OracleRef object, and obtain the appropriate
  instance type for those UDTs that have an inheritance hierarchy
  from OracleRef objects

You will notice there is only one set of sample code for both managed ODP.NET and ODP.NET Core. That’s because the code is EXACTLY the same. These two providers share the same UDT APIs and configuration options.

When compared to unmanaged ODP.NET, there is significant parity in features and APIs with managed ODP.NET and ODP.NET Core. This near-parity makes migrating existing unmanged ODP.NET UDT apps to managed ODP.NET or ODP.NET Core simpler. For many apps, migration involves just searching and replacing a few lines of code.

UDT Differences among ODP.NET Providers

Managed ODP.NET and ODP.NET Core support most of the same UDT APIs and features as unmanaged ODP.NET. There are some API and configuration differences. Let me detail them.

XML Configuration Files Not Supported

Managed ODP.NET and ODP.NET Core do not support Oracle custom type mapping via XML configuration files, such as in app.config or web.config. For these providers, custom type mapping occurs through using the OracleCustomTypeMapping attribute.

OracleUdt FromCustomObject and ToCustomObject Method Changes

The unmanaged ODP.NET OracleUdt FromCustomObject and ToCustomObjectmethod declarations are:

• FromCustomObject(OracleConnection con, IntPtr pObject)
• ToCustomObject(OracleConnection con, IntPtr pObject)

For managed ODP.NET and ODP.NET Core, the declarations are, respectively:

• FromCustomObject(OracleConnection con, object pObject)
• ToCustomObject(OracleConnection con, object pObject)

The second parameter is an object instead of a pointer.

OracleUdt Static Methods

OracleUdt static methods, GetValue, IsDBNull, SetValue, and their overloads all have one pointer (InPtr) parameter in unmanaged ODP.NET. For managed ODP.NET and ODP.NET Core, the pointer is replaced with an object that represents the UDT. In unmanaged ODP.NET, the static methods are:

GetValue

• GetValue(OracleConnection con, IntPtr pUdt, string attrName)
• GetValue(OracleConnection con, IntPtr pUdt, int attrIndex)
• GetValue(OracleConnection con, IntPtr pUdt, string attrName, out object statusArray)
• GetValue(OracleConnection con, IntPtr pUdt, int attrIndex, out object statusArray)

IsDBNull

• IsDBNull(OracleConnection con, IntPtr pUdt, string attrName)
• IsDBNull(OracleConnection con, IntPtr pUdt, int attrIndex)

SetValue

• SetValue(OracleConnection con, IntPtr pUdt, string attrName, object value)
• SetValue(OracleConnection con, IntPtr pUdt, int attrIndex, object value)
• SetValue(OracleConnection con, IntPtr pUdt, string attrName, object value, object statusArray)
• SetValue(OracleConnection con, IntPtr pUdt, int attrIndex, object value, object statusArray)

The managed ODP.NET and ODP.NET Core static methods are, respectively:

GetValue

• GetValue(OracleConnection con, object udt, string attrName)
• GetValue(OracleConnection con, object udt, int attrIndex)
• GetValue(OracleConnection con, object udt, string attrName, out object statusArray)
• GetValue(OracleConnection con, object udt, int attrIndex, out object statusArray)

IsDBNull

• IsDBNull(OracleConnection con, object udt, string attrName)
• IsDBNull(OracleConnection con, object udt, int attrIndex)

SetValue

• SetValue(OracleConnection con, object udt, string attrName, object value)
• SetValue(OracleConnection con, object udt, int attrIndex, object value)
• SetValue(OracleConnection con, object udt, string attrName, object value, object statusArray)
• SetValue(OracleConnection con, object udt, int attrIndex, object value, object statusArray)

OracleConnection Class Changes

Managed ODP.NET and ODP.NET Core do not support the following OracleConnection method:

• FlushCache()

OracleRef Class Changes

Managed ODP.NET and ODP.NET Core do not support object caches and some additional functionality that do not have a managed code implementation. Thus, the following unmanaged ODP.NET OracleRef class members are NOT supported in managed and core ODP.NET:

Constructors

• OracleRef(OracleConnection conn, string udtTypeName, string objTableName)
• OracleRef(OracleConnection conn, string hexStr)

Methods

• Flush()
• GetCustomObject(OracleUdtFetchOption fetchOption, int depthLevel)
• GetCustomObjectForUpdate(bool bWait)
• GetCustomObjectForUpdate(bool bWait, int depthLevel)
• Lock(bool bWait)

Properties

• HasChanges {get}
• ObjectTableName {get}

The following OracleRef class members are supported in a modified manner:

Migrating from Unmanaged ODP.NET to Managed ODP.NET or ODP.NET Core

The following changes are required to migrate UDT applications from unmanaged ODP.NET to either managed ODP.NET or ODP.NET Core:

Assembly

Reference managed ODP.NET or ODP.NET Core assembly (i.e. Oracle.ManagedDataAccess.dll) in your .NET project.

Namespace

Use the Oracle.ManagedDataAccess namespace in your source files, including Oracle.ManagedDataAccess.Client and Oracle.ManagedDataAccess.Types.

Configuration

Use OracleCustomTypeMapping attribute to define the custom type mapping.

APIs

Use the managed ODP.NET and ODP.NET Core equivalent APIs, which include:

• OracleUdt FromCustomObject and ToCustomObject methods in the custom class
• OracleUdt static methods
• OracleRef class invocation, including constructors, properties, and methods

Oracle Developer Tools for Visual Studio

Oracle Developer Tools (ODT) for Visual Studio is a popular design-time tool. It is a graphical user interface to perform common developer database administration tasks and to develop Oracle .NET apps. At this time, ODT do not support UDTs nor custom type code generation using managed ODP.NET nor ODP.NET Core.

However, developers can still migrate generated unmanaged ODP.NET custom code to use managed ODP.NET or ODP.NET Core.

Next Steps and What’s Next

You can try the new UDT support in managed ODP.NET and ODP.NET Core to develop object-relational apps. The new managed ODP.NET 21c release includes new binary JSON data type, Client Initiated Continuous Query Notification, sharding, and and more database features for .NET apps.

Looking ahead, we’re working on many exciting things we hope to announce in the near future. ODT will enable using UDTs and generate custom type code with managed ODP.NET in a future release. ODP.NET Core will add more Linux distribution certifications. All ODP.NET provider types will enhance their Docker support. Oracle will certify .NET 6 and EF Core 6 by the end of 2021.

Stay tuned!