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 objectSpatial-UDT.cs
: Map and fetch Oracle Spatial types as custom typesVArray.cs
: Map, fetch, and manipulate aVARRAY
as a custom objectNested-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 REFsRef-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 ToCustomObject
method 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
andToCustomObject
methods in the custom classOracleUdt
static methodsOracleRef
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!