Oracle Data Provider for .NET : Part III

ODP.NET Types Overview

ODP.NET types represent Oracle native types as a structure or as a class. For example, an OracleDecimal holds up to 38 precisions while a .NET Decimal holds only up to 28 precisions.

Table 3.2 lists all the Oracle native types supported by ODP.NET and their corresponding ODP.NET type. The third column lists the .NET Framework datatype that corresponds to the Value property of each ODP.NET Type.

Table 3-2 Oracle Native Types Supported by ODP.NET  

Oracle Native Type ODP.NET Type .NET Framework 
BFILE   OracleBFile class  System.Byte[]
BLOB  OracleBlob class System.Byte[]
CHAR OracleString structure System.String
CLOB  OracleClob class System.String
DATE  OracleDate structure System.DateTime
INTERVAL DAY TO SECOND  OracleIntervalDS structure System.TimeSpan
INTERVAL YEAR TO MONTH   OracleIntervalYM structure System.Int64
LONG  OracleString structure System.String
LONG RAW  OracleBinary structure System.Byte[]
NCLOB  OracleClob class System.String
NCHAR  OracleString structure System.String
NUMBER  OracleDecimal structure System.Decimal
NVARCHAR2  OracleString structure System.String
RAW OracleBinary structure System.Byte[]
REF CURSOR  OracleRefCursor class Not Applicable
TIMESTAMP OracleTimeStamp structure System.DateTime
TIMESTAMP WITH LOCAL TIME ZONE OracleTimeStampLTZ structure System.DateTime
TIMESTAMP WITH TIME ZONE  OracleTimeStampTZ structure System.DateTime
UROWID OracleString structure System.String
VARCHAR2  OracleString structure System.String
XMLType OracleXmlType class System.String

Obtaining Data From an OracleDataReader

The ExecuteReader method of the OracleCommand object returns an OracleDataReader object, which is a read-only, forward-only result set.

Typed OracleDataReader Accessors
The OracleDataReader provides two types of typed accessors:

.NET Type Accessors
Table 3.3 lists all the Oracle native database types that ODP.NET supports and the corresponding .NET Type that best represents the Oracle native type. The third column indicates the valid typed accessor that can be invoked for an Oracle native type to be obtained as a .NET type. If an invalid typed accessor is used for a column, an InvalidCastException is thrown. Oracle native datatypes depend on the version of the database; therefore, some datatypes are not available in earlier versions of Oracle. 

Table 3-3 .NET Type Accessors  

Oracle Native Datatype  .NET Type Typed Accessor
BFILE System.Byte[ ] GetBytes
BLOB System.Byte[ ] GetBytes
CHAR System.String GetString
CLOB System.String GetString
DATE System.DateTime GetDateTime
INTERVAL (DS) System.Interval GetTimeSpan
INTERVAL (YM) System.Interval GetTimeSpan


System.String GetString
LONG RAW System.Byte[ ] GetBytes
NCHAR System.String GetString
NCLOB System.String GetString
NUMBER System.Decimal GetDecimal
NVARCHAR2 System.String GetString
RAW System.Byte[ ] GetBytes
ROWID System.String GetString
TIMESTAMP System.TimeStamp GetTimeStamp
TIMESTAMP WITH TIME ZONE System.TimeStamp GetTimeStamp
UROWID System.String GetString
VARCHAR2 System.String GetString
XMLType System.String

ODP.NET Type Accessors

ODP.NET exposes provider-specific types that natively represent the datatypes in Oracle. In some cases, these ODP.NET types provide better performance and functionality that is not available to the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader by calling their respective typed accessor.

Table 3.4 lists the valid type accessors that ODP.NET uses to obtain ODP.NET Types for an Oracle native type.

Table 3-4 ODP.NET Type Accessors  

Oracle Native Database Type ODP.NET Type Typed Accessor
BFILE OracleBFile GetOracleBFile
BLOB OracleBlob
GetOracleBlob GetOracleBlobForUpdate
CHAR OracleString GetOracleString
CLOB OracleClob OracleClob OracleString GetOracleClob GetOracleClobForUpdate GetOracleString
DATE OracleDate GetOracleDate
INTERVAL (DS) OracleIntervalDS GetOracleIntervalDS
INTERVAL (YM) OracleIntervalYM GetOracleIntervalYM
LONG OracleString GetOracleString
LONG RAW OracleBinary GetOracleBinary
NCHAR OracleString GetOracleString
NCLOB OracleString GetOracleString
NUMBER  OracleDecimal GetOracleDecimal
NVARCHAR2 OracleString GetOracleString
RAW OracleBinary GetOracleBinary
ROWID  OracleString GetOracleString
TIMESTAMP  OracleTimeStamp GetOracleTimeStamp
UROWID OracleString GetOracleString
VARCHAR2 OracleString GetOracleString
XMLType OracleString OracleXmlType GetOracleString 

Obtaining LONG and LONG RAW Data

When an OracleDataReader is created containing a LONG or LONG RAW column type, OracleDataReader determines whether this column data needs to be fetched immediately or not, by checking the value of the InitialLONGFetchSize property of the OracleCommand that created the OracleDataReader.

By default, InitialLONGFetchSize is set to 0. If the InitialLONGFetchSize property value of the OracleCommand is left as 0, the entire LONG or LONG RAW data retrieval is deferred until that data is explicitly requested by the application. If the InitialLONGFetchSize property is set to a nonzero value, the LONG or LONG RAW data is immediately fetched up to the number of characters or bytes that the InitialLONGFetchSize property specifies.

ODP.NET does not support CommandBehavior.SequentialAccess. Therefore, LONG and LONG RAW data can be fetched in a random fashion. 

Obtain data beyond InitialLONGFetchSize bytes or characters, one of the following must be in the select list:

  • primary key 
  • ROWID 
  • unique columns - (defined as a set of columns on which a unique constraint has been defined or a unique index has been created, where at least one of the columns in the set has a NOT NULL constraint defined on it).

The requested data is fetched from the database when the appropriate typed accessor method (GetOracleString or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.

In order to fetch the data in a non-defer mode or when the columns in the select list do not have a primary key column, a ROWID, or unique columns, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the amount of bytes or characters needed to be retrieved. 

Continue article...