OracleDataAdapter Safe Type Mapping

The ODP.NET OracleDataAdapter provides the Safe Type Mapping feature because the following Oracle datatypes can potentially lose data when converted to their closely related .NET type:

NUMBER
DATE
TimeStamp (refers to all TimeStamp objects)
INTERVAL DAY TO SECOND

When populating Oracle data containing any of these types into a .NET DataSet there is a possibility of data loss. The OracleDataAdapter Safe Type Mapping feature prevents data loss. By setting the SafeMapping property appropriately, these types can be safely represented in the DataSet, as either of the following:

.NET byte[] in Oracle format
.NET String

Potential Data Loss

The following sections provide more detail about the types and circumstances where data can be lost.

Oracle NUMBER Type to .NET Decimal Type

The Oracle datatype NUMBER can hold up to 38 precisions whereas .NET Decimal type can hold up to 28 precisions. If a NUMBER datatype that has more than 28 precisions is retrieved into .NET decimal type, it loses precision.

Table 3-14 lists the maximums and minimums for Oracle NUMBER and .NET Decimal.

Table 3-14 Oracle NUMBER to .NET Decimal Comparisons

	Oracle NUMBER	.NET Decimal
Maximum	9.9999999999999999999999999999999999999 e125	79,228,162,514,264,337,593,543,950,335
Minimum	-9.9999999999999999999999999999999999999 e125	-79,228,162,514,264,337,593,543,950,335

Oracle Date Type to .NET DateTime Type

The Oracle datatype DATE can represent dates in BC whereas .NET DateTime cannot. If a DATE that goes to BC get retrieved into .NET DateTime type, it loses data.

Table 3-15 lists the maximums and minimums for Oracle Date and .NET DateTime.

Table 3-15 Oracle Date to .NET DateTime Comparisons

	Oracle Date	.NET DateTime
Maximum	Dec 31, 9999 AD	Dec 31, 9999 AD 23:59:59.9999999
Minimum	Jan 1, 4712 BC	Jan 1, 0001 AD 00:00:00.0000000

Oracle TimeStamp Type to .NET DateTime Type

Similarly to DATE, Oracle datatype TimeStamp can represent a date in BC whereas .NET DateTime type cannot. If a TimeStamp that goes to BC is retrieved into .NET DateTime type, it loses data. Oracle TimeStamp type can represent values in units of e-9, whereas the .NET DateTime type can only represent values in units of e-7. The Oracle TimeStamp with time zone datatype can store time zone information whereas .NET DateTime cannot.

Table 3-16 lists the maximums and minimums for Oracle TimeStamp and .NET DateTime.

Table 3-16 Oracle TimeStamp to .NET DateTime Comparisons

	Oracle TimeStamp	.NET DateTime
Maximum	Dec 31, 9999 AD 23:59:59.999999999	Dec 31, 9999 AD 23:59:59.9999999
Minimum	Jan 1, 4712 BC 00:00:00.000000000	Jan 1, 0001 AD 00:00:00.0000000

Oracle INTERVAL DAY TO SECOND to .NET TimeSpan

Similarly to DATE, the Oracle datatype INTERVAL DAY TO SECOND can represent dates in BC, whereas the .NET TimeSpan type cannot. If an INTERVAL DAY TO SECOND that goes to BC is retrieved into .NET TimeSpan type, it loses the data. The Oracle INTERVAL DAY TO SECOND type can represent values in units of e-9 whereas .NET TimeSpan type can only represent values in units of e-7.

Table 3-17 lists the maximums and minimums for Oracle INTERVAL DAY TO SECOND and .NET DateTime.

Table 3-17 Oracle INTERVAL DAY TO SECOND to .NET TimeSpan Comparisons

	Oracle INTERVAL DAY TO SECOND	.NET TmeSpan
Maximum	+999999999 23:59:59.999999999	+10675199 02:48:05.4775807
Minimum	-999999999 23:59:59.999999999	-10675199 02:48:05.4775808

SafeMapping Property

By default, Safe Type Mapping is disabled.

Using Safe Type Mapping

To use the Safe Type Mapping functionality, the OracleDataAdapter.SafeMapping property must be set with a hashtable of key-value pairs. The key-value pairs must map database table column names (of type string) to a .NET type (of type Type). ODP.NET supports safe type mapping to byte[] and String types. Any other type mapping causes an exception.

In situations where the column names are not known at design time, an asterisk ("*") can be used to map all occurrences of database types to a safe .NET type where it is needed. If both the valid column name and the asterisk are present, the column name is used.

Note:

Database table column names are case sensitive.
Column names in the hashtable that correspond to invalid column names are ignored.

Mapping to a .NET String

The safe type mapping as a string is more readable without further conversion. Converting certain Oracle datatypes to a string requires extra conversion, which can be slower than converting it to a byte[]. Conversion of .NET strings back to ODP.NET types relies on the formatting information of the session.