User-defined types (UDTs)|V1.3.0|OceanBase Connector/NET|docs|OceanBase Documentations

OceanBase Connector/NET supports user-defined types (UDTs) in Oracle mode, covering common scenarios such as OBJECT and VARRAY (including nested VARRAY), with usage aligned to the custom type mapping model of ODP.NET.

Note

UDT support is available in OceanBase Oracle mode.
The text protocol (UseOraclePrepareExecute=false) and the binary or binary/character protocol can be used. When containing non-ASCII characters or complex nesting, it is recommended to prefer Prepare() or set UseOraclePrepareExecute=true.

Basic concepts

Concepts	Description
`IOracleCustomType`	Custom OBJECT type must implement this interface, which is responsible for interacting with the`OracleUdt`Reads and writes attributes between them.
`IOracleCustomTypeFactory`	Creates a factory interface for instantiating custom types.
`[OracleCustomTypeMapping("SCHEMA.TYPE")]`	Maps CLR types or factories to database type names. It can be attached to custom type classes or to independent factories.
`IOracleArrayTypeFactory`	Factory interface for collection types such as VARRAY and nested tables, used to create array instances.
`OracleDbType.Object` / `OracleDbType.Array`	Parameter binding corresponds to the OBJECT and set types, respectively.
`OracleParameter.UdtTypeName`	The UDT type name on the protocol side. This usually needs to be explicitly set in the binary path.
`IOracleNullable`	NULL Sentinel API. Available after implementation.`Type.Null`Indicates that the entire UDT column is a database NULL value. This also complies with`System.Data.SqlTypes.INullable`.

Define a custom OBJECT

The following example demonstrates the minimum OBJECT mapping. Replace the type name, schema, and attributes with your actual database table definitions.

using OceanBase;

[OracleCustomTypeMapping("SCOTT.PERSON_O")]
public sealed class Person : IOracleCustomType, IOracleCustomTypeFactory, IOracleNullable
{
	[OracleObjectMapping("NAME")]
	public string? Name { get; set; }

	[OracleObjectMapping("AGE")]
	public int? Age { get; set; }

	public bool IsNull { get; private set; }

	public static Person Null => new() { IsNull = true };

	public void FromCustomObject(OracleConnection connection, object udt)
	{
		OracleUdt.SetValue(connection, udt, "NAME", Name);
		OracleUdt.SetValue(connection, udt, "AGE", Age);
	}

	public void ToCustomObject(OracleConnection connection, object udt)
	{
		Name = (string?)OracleUdt.GetValue(connection, udt, "NAME");
		Age = OracleUdt.IsDBNull(connection, udt, "AGE")
			? null
			: Convert.ToInt32(OracleUdt.GetValue(connection, udt, "AGE"));
	}

	public IOracleCustomType CreateObject() => new Person();
}

Note

A common practice in ODP.NET is not to directly derive the custom type from [OracleCustomTypeMapping], but instead declare the mapping on a separate IOracleCustomTypeFactory. This pattern is supported in V1.3.0. After a SELECT statement, you can use GetValue to materialize the corresponding CLR type.

Parameter binding and retrieval

using var connection = new OracleConnection(connectionString);
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "insert into t_person(id, person) values(:id, :person)";
command.Parameters.Add("id", OracleDbType.Int32).Value = 1;

var personParam = new OracleParameter("person", OracleDbType.Object)
{
	UdtTypeName = "SCOTT.PERSON_O",
	Value = new Person { Name = "Alice", Age = 28 }
};
command.Parameters.Add(personParam);
command.ExecuteNonQuery();

command.Parameters.Clear();
command.CommandText = "select person from t_person where id = :id";
command.Parameters.Add("id", OracleDbType.Int32).Value = 1;

using var reader = command.ExecuteReader();
if (reader.Read())
{
	var person = reader.GetValue(0) as Person;
	Console.WriteLine($"{person?.Name}, {person?.Age}");
}

When writing an entire UDT column as NULL in the database, you can use a sentinel object:

personParam.Value = Person.Null; // Implement IOracleNullable and set IsNull to true.

VARRAY / Collection types

Collection types register element arrays with database type names through IOracleArrayTypeFactory, using the parameter OracleDbType.Array. If a VARRAY attribute is nested within an OBJECT, the client must also register the corresponding array factory so that the type name can be looked up during serialization using the text protocol.

DeriveParameters

For stored procedures containing UDT or COLLECTION parameters, you can use:

OracleCommandBuilder.DeriveParameters(command);

The driver derives the parameter direction and OracleDbType (including OBJECT and collection types) based on the procedure metadata. If the procedure does not exist, a specific exception is thrown instead of returning an empty parameter set.

Considerations

In binary protocol, set UdtTypeName correctly. Omitting the type name may cause protocol errors (such as OBA-50028).
Reading a STRUCT without a mapping will fail. You must first complete the registration in [OracleCustomTypeMapping].
National character set types such as NCHAR and NVARCHAR2 do not follow the connection-level Character Set.
The UDT capability in ArrayBinding scenarios is still subject to limitations. Please verify according to the actual version.
For a more complete type mapping, see Data types overview.