Oracle User-Defined Types (UDTs) and .NET Custom Types

Required Custom Type Implementations

This section lists the required implementations for a custom .NET class or structure.

• Oracle.DataAcess.Types.IOracleCustomType interface implementation

  This interface is used for conversions between custom types and Oracle UDTs.

  The interface methods are implemented using the static methods of the OracleUdt class.

• Custom Type Factories

  A custom type factory is used to create an instance of a custom type. A custom type factory is an implementation of either the IOracleCustomTypeFactory interface, the IOracleArrayTypeFactory interface, or both interfaces, as follows:

  • To create a custom type that represents an Oracle Object, the custom type or a separate custom type factory class must implement the Oracle.DataAccess.Types.IOracleCustomTypeFactory interface.

  • To create a custom type that represents an Oracle Collection, the custom type or a separate custom type factory class must implement the Oracle.DataAccess.Types.IOracleCustomTypeFactory interface and the Oracle.DataAccess.Types.IOracleArrayTypeFactory interface.

  • To create an array type that represents an Oracle Collection, a custom type factory class must implement the Oracle.DataAccess.Types.IOracleArrayTypeFactory interface.

• Custom Type Member Mapping Attributes

  The custom type member mapping attributes specify the mapping between custom type members and either Oracle object attributes or Oracle collection elements.

  There are two types of custom type member mapping attributes:

  • OracleObjectMappingAttribute

    This attribute specifies the mapping between custom type members and Oracle object attributes for custom types that represent Oracle objects. This attribute must be applied to each custom type member (either field or property) that represents an Oracle Object attribute.

    Note:

    Not all Oracle object attributes need to be mapped to custom type members. If there is no OracleObjectMappingAttribute for a particular object attribute, ODP.NET ignores that object attribute when converting between Oracle objects and custom types.

  • OracleArrayMappingAttribute

    This attribute specifies the custom type member that stores the elements of an Oracle collection for custom types representing Oracle collections.The attribute must be specified on only one of the custom type members.

• Oracle.DataAcess.Types.INullable interface implementation

  This interface is used to determine if an instance of a custom type represents a null UDT. The IsNull property of the interface enables applications and ODP.NET to determine whether or not the UDT is null.

• Static Null field

  The public static Null property is used to return a null UDT. This property returns a custom type with an IsNull property that returns true.

Optional Custom Type Implementations

The following are optional:

• IXMLSerializable

  The IXMLSerializable interface is used in the .NET 2.0 framework to enable conversion between the custom type and its XML representation.This interface is only used if the serialization and deserialization of a custom type is needed in the DataSet.

• Static Parse and Public ToString methods

  These methods enable conversion between the custom type and its string representation.

  These methods are invoked when a DataGrid control is used to accept changes and display instance values.

• Type Inheritance

  Type Inheritance refers to the process of deriving an Oracle UDT in the database from a super type.

  If the custom type represents an Oracle UDT that is derived from a super type, the custom class should follow the same type hierarchy, that is, the custom class should be derived from another custom class that represents the super type defined in the database.

• OracleCustomTypeMappingAttribute

  The OracleCustomTypeMappingAttribute object specifies the mapping between a custom type (or an array type) and an Oracle UDT.

  There must be a unique custom type factory for each Oracle UDT used by the application as follows:

  • Oracle Object Types:

    The custom type factory must return a custom type that only represents the specified Oracle Object Type.

  • Oracle Collection Types:

    The custom type factory may return a custom type that can be used by other Oracle Collection Types. This is common when an array type is used to represent an Oracle Collection, for example, when an int[] is used to represent a collection of NUMBERs.

  If the OracleCustomTypeMappingAttribute is not specified, then custom type mappings must be specified through XML configuration files, that is, machine.config, and either app.config for Windows applications or web.config for web applications.

Using a Custom Type Factory to Specify Custom Type Mappings

The application can specify a custom type mapping using a custom type factory. The application supplies the name of the Oracle UDT, in the format schema_name.type_name, to an OracleCustomTypeMappingAttribute object and applies the name to the corresponding custom type factory. A custom type factory is a class or struct that implements either or both the IOracleCustomTypeFactory and IOracleArrayTypeFactory interfaces.

Note that for each Oracle UDT used by the application, there must be a unique custom type factory. Additionally, for Oracle Object Types, the custom type factory must return a custom type that uniquely represents the specified Oracle Object Type. For Oracle Collection Types, the custom type factory returns a custom type that can be used by other Oracle Collection Types. This is common when an custom type that is an array type represents an Oracle Collection, that is, when an int[] is used to represent a collection of NUMBERs.

At run time, using reflection programming, ODP.NET discovers all the custom type mappings specified by the application through the OracleCustomTypeMappingAttribute object.

Using XML in Configuration Files to Specify Custom Type Mappings

The application can specify a custom type mapping with XML in configuration files, for example: using machine.config, and either app.config for Windows applications or web.config for web applications.

The custom type mappings must be specified in the oracle.dataaccess.client configuration section group. Each custom type mapping must be added to the collection of custom type mappings using the XML element <add>.

Each custom type mapping is consists of a name attribute and a value attribute. The name attribute may be any user-specified name that represents the custom type mapping. The value attribute must begin with udtMapping and be followed by the required and optional attributes listed below.

 <oracle.dataaccess.client>
    <settings>
      <add name="Person" value="udtMapping factoryName='Sample.PersonFactory,
          Sample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'
          typeName='PERSON' schemaName='SCOTT' dataSource='oracle'"/>
      <add name="Student" value="udtMapping factoryName='Sample.StudentFactory,
          Sample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'
          typeName='STUDENT' schemaName='SCOTT'"/>
    </settings>
  </oracle.dataaccess.client>

Using Custom Type Mappings

During data retrieval, the application uses the custom type mappings to convert an Oracle UDT to a custom type. When data is provided back to the database through an input or input/output parameter, or by an update through an Oracle REF, the application uses the mappings to convert the custom type to an Oracle UDT.

In the case of input and input/output parameters, the application must also set the OracleParameter UdtTypeName property to the user-defined type name of the parameter.

In certain cases, where Oracle UDTs are part of a type hierarchy, the custom type must be instantiated as a specific type in the type hierarchy. The Oracle UDT provided by the custom type mapping must a subtype of the Oracle UDT specified by the OracleParameter UdtTypeName property.

For example, the parameter for a stored procedure is of type, SCOTT.PERSON and has a subtype, SCOTT.STUDENT. The application has a custom class instance that represents SCOTT.STUDENT. The UdtTypeName is set to SCOTT.PERSON, but the custom type mapping indicates that the custom class is mapped to SCOTT.STUDENT and overrides the UdtTypeName when it instantiates the Oracle UDT. Thus, ODP.NET instantiates and binds Oracle UDTs appropriately when the custom object represents an Oracle UDT that is a subtype of the parameter type.

Guidelines for Binding UDT Input and Output Parameters

Developers must consider the following when using UDT parameter bindings with an OracleParameter object.

• The UdtTypeName property must be set. Binding is based on the UdtTypeName property regardless of the parameter direction.

  Note:

  The UdtTypeName may differ from the Oracle UDT specified in the custom type mapping. This occurs when the parameter type is a super type of the Oracle UDT that the custom type represents.

• In case of Input/Output binding, the behavior is the same as Input and Output parameters.

• For Input parameter values, the bind value is converted to the UDT specified by the custom type mapping.

• For Output parameters:

  • If the value being returned is an Oracle Object or Collection, it is converted to a custom type or array type as specified by the custom type mapping. The value returned is always a custom type or an array type, regardless of whether the property most recently set was DbType or OracleDbType.

  • If the value being returned is a REF, then no custom type mapping is required.

UDT Input Parameter Binding with OracleParameters

Only certain combinations of these OracleParameter property values, DbType, OracleDbType, and UdtTypeName, can exist on the OracleParameter object. OracleParameter objects cannot be set to combinations that are not listed.

Table 3-24 describes the valid ways of binding input parameters for Oracle UDTs.

The last column indicates the Oracle type that ODP.NET converts the OracleParameter value to before binding.

UDT Output Parameter Binding with OracleParameters

Only certain combinations of these OracleParameter property values, DbType, OracleDbType, and UdtTypeName, can exist on the OracleParameter object. OracleParameter objects cannot be set to combinations that are not listed.

Table 3-25 shows the supported ODP.NET output parameter bindings of Oracle database objects.

The last column indicates the type that ODP.NET converts the OracleParameter value to before binding.

StatementCacheWithUdts

StatementCacheWithUdts specifies whether or not ODP.NET caches Oracle UDTs retrieved by a SELECT statement along with the statement when it is returned to the statement cache. Possible values are 1 - Yes (the default) or 0 - No.

For the value of 1, the Oracle UDTs are cached along with the statements. Therefore, the memory that contained the UDTs can be re-used; subsequent executions of the same statement do not require additional memory. This may result in an overall higher performance.

For the value of 0, ODP.NET frees the memory for the retrieved Oracle UDTs before the statement is returned to the statement cache. This may result in poorer performance because subsequent executions will require new memory allocations.

UdtCacheSize

UdtCacheSize specifies the size of the object cache for each connection that ODP.NET uses when retrieving and manipulating Oracle UDTs. The value for this setting must be specified in kilobytes (KB) with the default 4096KB, equivalent to 4 MB.

This configuration setting is used to determine how frequently the objects in the object cache will be purged (using an LRU approach) as the limit of the object cache size approaches.