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