Oracle Data Provider for .NET supports Oracle object types or user-defined types (UDTs), which are defined in the Oracle database.

There are two kinds of UDTs:

• Object types (Oracle Object)

• Collection types (which can be VARRAY types or nested table types)

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

The term UDT is used interchangeably with Oracle object types and abstract data types (ADTs).

The name of the Oracle UDT is case-sensitive and must be in the form schema_name.type_name.

UDT samples in installations done using ODAC Oracle Universal Installer are provided in the ORACLE_BASE\\ORACLE_HOME\ODP.NET\Samples\UDT directory.

Oracle Data Provider for .NET supports UDTs by representing Oracle UDTs defined in the database as .NET types, that is, custom types. For every Oracle UDT that the application wishes to fetch and manipulate, one custom type factory and one custom type are needed. The custom factory class is solely responsible for instantiating the custom type. ODP.NET uses the interfaces implemented on the custom factory classes to instantiate custom types at run time. Custom types define the mapping between the Oracle UDT attributes or elements to the .NET members. ODP.NET uses the interfaces implemented on the custom type instances to transfer values between the Oracle UDT and the custom type at run time.

Custom types can be .NET classes or structures. They can represent either Oracle Objects or Oracle Collections. Custom types can be implemented manually by the application developer or generated through an ODP.NET code generation tool.

Once the factory class and the custom type are defined and meet the implementation requirements, the application may set ODP.NET to automatically discover the mapping between the Oracle UDT and the custom type. This discovery process is based on the attribute that is applied on the custom factory class. Alternatively, the application can provide an explicit mapping through a configuration file.

Oracle Collections can be represented as an array of .NET Types. For example, an Oracle Collection type of NUMBER can be mapped to an int[]. Moreover, an Oracle Collection type of an Oracle UDT can be mapped to an array of the custom type.

Custom types must adhere to certain requirements in order for ODP.NET to represent Oracle UDTs as custom types. These requirements are as follows:

After creating a custom type, the application must specify a custom type mapping that maps the custom type to an Oracle UDT in the database. This can be done using a custom type factory or XML in configuration files.

Using XML to specify custom type mappings has priority, if both techniques have been implemented. At run time, if ODP.NET finds custom type mappings specified in configuration files, it ignores any custom type mappings specified through the OracleCustomTypeMappingAttribute object. If a .NET application dynamically loads .NET assemblies, which contain .NET classes that Oracle UDTs are mapped to, then the mapping between .NET classes and Oracle UDTs must be configured using a .NET config file.

Custom type mappings cannot be specified using synonyms, regardless of whether or not the mapping is provided through the OracleCustomTypeMappingAttribute object or the XML configuration file.

This section contains these topics:

• "Using a Custom Type Factory to Specify Custom Type Mappings"

• "Using XML in Configuration Files to Specify Custom Type Mappings"

 <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>

ODP.NET can convert between Oracle UDTs and custom types, if the proper attribute mappings are specified and the custom types are defined properly.

ODP.NET performs a conversion whenever an Oracle UDT is fetched as:

• In, out, in/out parameters bound for SQL or PL/SQL execution

  The DbType property of OracleParameter must be set to DbType.Object or the OracleDbType property must be set to OracleDbType.Object or OracleDbType.Array.

  For parameters that are user-defined types, the UdtTypeName property of the OracleParameter object must be always set to the parameter type.

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

• Column value retrieved from an OracleDataReader object

  If the application requests for the value either through the GetValue, GetValues, GetOracleValue, GetOracleValues, GetProviderSpecificValue, or GetProviderSpecificValues methods or the Item[] property for a UDT column, ODP.NET finds the corresponding custom type that represents the Oracle UDT and carries out the proper conversion.

• Part of a Resultset that populates the DataSet

  If the application populates the DataSet with a result that contains UDTs using the Fill method on the OracleDataAdapter, the DataSet is populated with custom types that represent Oracle UDTs. With ADO.NET 2.0, the DataSet is populated with custom types for UDT columns regardless of whether the ReturnProviderSpecificTypes on the OracleDataAdapter is set to true or false.

• A Object referenced through a REF

  When an Object referenced by a REF is retrieved, the custom type that represents the Oracle UDT is returned.

  The application can use the OracleUdtFetchOption method to control the copy of the Object that is returned as follows:

  • If the OracleUdtFetchOption.Cache option is specified and a cached copy of the object exists, the cached copy is immediately returned. If no cached copy exists, the latest object copy from the database is cached and returned.

  • If the OracleUdtFetchOption.Server option is specified, the latest object copy from the database is cached and returned. If the object is already cached, the latest object copy overwrites the existing one.

  • If the OracleUdtFetchOption.TransactionCache option is specified, there are two possibilities within the same transaction:

    • If the object copy was previously retrieved using the Server or TransactionCache option, the TransactionCache option behavior becomes equivalent to the Cache option behavior.

    • If the object copy was not previously retrieved using the Server or TransactionCache option, the TransactionCache option behavior becomes equivalent to the Server option behavior.

Table 3-21 lists valid mappings of attributes (for objects) and elements (for collections), between Oracle UDT types and custom object types which can be either .NET types or Oracle provider-specific types (ODP.NET types).

Oracle collections do not have to map to a custom class. They can map to arrays of a specific type. Table 3-21 indicates those collections with elements of a specified Oracle type that can map to arrays of a .NET Type or a provider-specific type. For example, if an Oracle Collection is a VARRAY of NUMBER(8), it can map to a typeof(int[]). This eliminates the need to construct a class that only holds an int[].

For .NET 2.0, Oracle Collections can be mapped to Nullable types. This allows .NET 2.0 applications to obtain a nullable int[] which can hold null values in the int[].

Note that Oracle UDT attributes and elements cannot be mapped to object or object[].

Notes:

1. Conversion from a System.Byte[] to a BFILE is not supported, and therefore, System.Byte[] only represents a BFILE in read-only scenarios.

In order to retrieve Oracle UDTs from the OracleDataReader, an application must specify a custom type mapping that determines the type that will represent the Oracle UDT. Once a custom type mapping has been specified and any necessary custom types have been created, the application can retrieve Oracle UDTs.

Table 3-22 shows the type and value returned from an OracleDataReader object based on the method invoked, the column type, and whether or not there is a valid Custom type mapping.

An OracleDataReader object can return metadata used to determine the custom type that represents an Oracle UDT when a .NET Type or Provider-Specific Type accessor is invoked. The same custom type is used when populating the DataSet using the OracleDataAdapter.Fill method.

Table 3-23 shows the values returned from the OracleDataReader GetFieldType and GetProviderSpecificFieldType methods that specify the .NET type of the column.

This section discusses using UDT output and input parameter bindings with an OracleParameter object.

This section contains these topics:

• Guidelines for Binding UDT Input and Output Parameters

• UDT Input Parameter Binding with OracleParameters

• UDT Output Parameter Binding with OracleParameters

The DataSet is a disconnected result set. With ADO.NET 2.0, both .NET types and provider-specific types can be used to populate the DataSet. This section describes the types used to populate the DataSet when the column is an Oracle UDT.

Table 3-26 lists the types that populate the DataSet column, based on the Oracle column type, the ReturnProviderSpecificTypes property of the DataAdapter, the existence of a custom type mapping, the DataSet column type, the DataSet column value, and the DataSet column null value.

ODP.NET supports invocation of methods defined for a UDT on the database. This can be accomplished by doing the following:

1. Set the CommandType as CommandType.StoredProcedure.

2. Set the CommandText as "type_name.procedure_name"

3. Execute the command using any of the Execute methods on the OracleCommand object.

For instance functions, the parameters are as follows:

• The first parameter must be the return value.

• The second parameter must be the UDT instance on which the instance method is invoked, which is the instance of the .NET custom object.

• Subsequent parameters are for the function.

For instance procedures, the first parameter must be the UDT instance.

For static methods, the UDT instance is not needed.

ODP.NET exposes two configuration settings to determine how ODP.NET handles Oracle UDTs.

• StatementCacheWithUdts

• UdtCacheSize

These configuration settings can be specified as machine-wide settings for a particular version of ODP.NET, using the registry key with the name that exists under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version. The configuration settings specified in the registry can be overridden if an entry is created in the machine.config for .NET framework-wide settings, or in the app.config or web.config for application-specific settings.