| Oracle® Data Provider for .NET Developer's Guide 11g Release 2 (11.2.0.1.2) Part Number E17357-04 |
|
|
View PDF |
| Oracle® Data Provider for .NET Developer's Guide 11g Release 2 (11.2.0.1.2) Part Number E17357-04 |
|
|
View PDF |
ODP.NET has the ability to represent Oracle UDTs found in the database as custom types in .NET applications. UDTs are useful in representing complex entities as a single object that can be shared among applications. Oracle products, such as Oracle Spatial and Oracle XML DB, use their own complex types frequently.
To represent Oracle UDTs as .NET custom types, applications must apply .NET attributes to custom classes and structs, and to their public fields and properties
To convert between UDTs and custom types, ODP.NET uses custom interfaces.
This section discusses the following topics:
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.
See Also:
"OracleRef Class"The term UDT is used interchangeably with Oracle object types and abstract data types (ADTs).
See Also:
Oracle Database Application Developer's Guide - Object-Relational Features for complete descriptions of object typesThe name of the Oracle UDT is case-sensitive and must be in the form schema_name.type_name.
UDT samples 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:
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.
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.
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.
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.
See Also:
Oracle Developer Tools for Visual Studio help sections on User-Defined Types Node, under Server Explorer for Visual Studio 2005 and Oracle Explorer for Visual Studio 2003, for further information on UDT mappingThis section contains these topics:
"Using a Custom Type Factory to Specify Custom Type Mappings"
"Using XML in Configuration Files 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.
Note:
The UDT name that is specified in theOracleCustomTypeMappingAttribute may not contain a period.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.
factoryName
The case-sensitive assembly qualified name of the custom type factory class or struct.
If the assembly that defines the custom type factory does not have a strong name, then a partial assembly name consisting of just the assembly name is sufficient. In the case of strongly named assemblies, a complete assembly name is required. It must include the assembly name, the Version, Culture, PublicKeyToken.
typeName
The case-sensitive name of the UDT defined in the database. By default all UDTs are created in the database with upper case names
schemaName
The case-sensitive schema in which the UDT is defined in the database. By default all schemas are created in the database with upper case names
dataSource
If specified, indicates that the custom type mapping applies only to Oracle UDTs defined in the database that the application connects to, as specified by the TNS name alias.
The Data Source is case-insensitive.
The following is an example of the format of the XML that can be specified in the configuration file for .NET 2.0:
<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>
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.
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-19 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-19 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[].
Table 3-19 Attribute Mappings Between UDTs and Custom Object Types
| Type of UDT Attribute or Element | .NET Type | ODP.NET Type |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Nested Table |
|
|
|
|
|
|
|
|
|
|
|
Object Type |
|
N/A |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Notes:
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-20 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.
Note:
PS Object refers to a provider-specific object.Table 3-20 Type and Value Returned from OracleDataReader Object
| OracleDataReader method/property invocation | Column Data Type | Custom Type Mapping | Value Returned for Oracle UDT | NULL Value Returned for Oracle UDT |
|---|---|---|---|---|
|
|
|
none |
Exception thrown |
Exception thrown |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
none | |
|
|
|
|
|
none | |
|
Exception thrown |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
none | |
|
|
|
|
|
none | |
|
|
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-21 shows the values returned from the OracleDataReader GetFieldType and GetProviderSpecificFieldType methods that specify the .NET type of the column.
Table 3-21 Values Returned from OracleDataReader Methods
| OracleDataReader Method/Property invocation | Column Data Type | Custom Type Mapping | Return Value |
|---|---|---|---|
|
|
|
none |
Exception thrown |
|
|
|
|
|
|
|
|
|
|
|
|
|
none | |
|
|
|
|
none |
Exception thrown |
|
|
|
|
|
|
|
|
|
|
|
|
|
none | |
|
This section discusses using UDT output and input parameter bindings with an OracleParameter object.
See Also:
"Parameter Binding"This section contains these topics:
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:
TheUdtTypeName 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.
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-22 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.
Table 3-22 Valid Ways to Bind Input Parameters for Oracle UDTs
| OracleParameter. Value | OracleParameter. DbType or OracleParameter. OracleDbType | OracleParameter. UdtTypeName | Custom Type Mappings | Oracle Type converted to before Binding |
|---|---|---|---|---|
|
|
|
not set |
none | |
Exception thrown |
|
|
|
|
none |
Exception thrown |
|
|
|
|
|
Specified UDT is instantiated. Value is bound as Object or Collection, based on the |
|
|
|
|
|
Specified UDT is instantiated. |
|
|
|
|
|
Specified UDT is instantiated. |
|
|
|
|
|
UDT specified by |
|
|
|
|
none | |
Exception thrown |
|
|
|
|
none | |
Exception thrown |
|
|
|
|
none | |
Exception thrown |
|
Char[] (HEX) | |
|
|
none | |
A |
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-23 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.
Table 3-23 Valid Ways to Bind Output Parameters for Oracle UDTs
| Type returned from Oracle | OracleParameter. DbType | OracleParameter. UdtTypeName | Custom Type Mappings | Type converted to |
|---|---|---|---|---|
|
|
|
not set |
none | |
Exception thrown |
|
|
|
|
none |
Exception thrown |
|
|
|
|
|
|
|
|
|
|
none | |
Exception thrown |
|
|
|
|
|
|
|
|
|
|
none | |
Exception thrown |
|
|
|
|
none | |
Exception thrown |
|
|
|
|
none | |
|
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-24 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.
Table 3-24 Types that Populate the DataSet with ADO.NET 2.0
| Oracle Column Type | ReturnProvider- SpecificTypes Property | Custom Type Mappings | DataSet Column Type | DataSet Column Value | DataSet Column Null Value |
|---|---|---|---|---|---|
|
|
|
none |
Exception thrown |
Exception thrown |
Exception thrown |
|
|
|
schema.type |
|
|
|
|
|
|
schema.type |
|
|
|
|
|
|
schema.type |
|
|
|
|
|
|
schema.type |
|
|
|
|
|
|
none | schema.type |
|
|
|
|
|
|
none | schema.type |
|
|
|
ODP.NET supports invocation of methods defined for a UDT on the database. This can be accomplished by doing the following:
Set the CommandType as CommandType.StoredProcedure.
Set the CommandText as "type_name.procedure_name"
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.
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. For details on configuring ODP.NET, see "Configuring Oracle Data Provider for .NET".
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 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.
|
 Copyright © 2002, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|