Oracle Data Provider for .NET : Part III

Before reading this article, please go through the following articles

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	.NETFramework Datatypes
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 GetChars
CLOB	System.String	GetString GetChars
DATE	System.DateTime	GetDateTime
INTERVAL (DS)	System.Interval	GetTimeSpan
INTERVAL (YM)	System.Interval	GetTimeSpan
LONG	System.String	GetString GetChars
LONG RAW	System.Byte[ ]	GetBytes
NCHAR	System.String	GetString GetChars
NCLOB	System.String	GetString GetChars
NUMBER	System.Decimal	GetDecimal
NVARCHAR2	System.String	GetString GetChars
RAW	System.Byte[ ]	GetBytes
ROWID	System.String	GetString GetChars
TIMESTAMP	System.TimeStamp	GetTimeStamp
TIMESTAMP WITH LOCAL TIME ZONE	System.TimeStamp	GetTimeStamp
TIMESTAMP WITH TIME ZONE	System.TimeStamp	GetTimeStamp
UROWID	System.String	GetString GetChars
VARCHAR2	System.String	GetString GetChars
XMLType	System.String System.Xml.XmlReader	GetString GetXmlReader

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 ODP.NET Type Accessors

OracleNativeDatabaseType	ODP.NET Type	Typed Accessor
BFILE	OracleBFile	GetOracleBFile
BLOB	OracleBlob OracleBlob OracleBinary	GetOracleBlob GetOracleBlobForUpdate GetOracleBinary
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
TIMESTAMP WITH LOCAL TIME ZONE	OracleTimeStampLTZ	GetOracleTimeStampLTZ
TIMESTAMP WITH TIME ZONE	OracleTimeStampTZ	GetOracleTimeStampTZ
UROWID	OracleString	GetOracleString
VARCHAR2	OracleString	GetOracleString
XMLType	OracleString OracleXmlType	GetOracleString GetOracleXmlType

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.

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

References

To gain a more comprehensive understanding of the subject, please read the next part

Oracle Data Provider for .NET : Part IV