Skip Headers
Oracle® Data Provider for .NET Developer's Guide
11g Release 1 (11.1)

Part Number B28375-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Obtaining Data from an OracleDataReader Object

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

This section provides the following information about the OracleDataReader object:

Typed OracleDataReader Accessors

The OracleDataReader class provides two types of typed accessors:

.NET Type Accessors

Table 3-10 lists all the Oracle native database types that ODP.NET supports, and the corresponding .NET types that can represent the Oracle native type. If more than one .NET type can be used to represent an Oracle native type, the first entry is the .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 data types depend on the version of the database; therefore, some data types are not available in earlier versions of Oracle Database.

Table 3-10 .NET Type Accessors

Oracle Native Data Type .NET Type Typed Accessor

BFILE

System.Byte[]

GetBytes

BINARY_DOUBLE

System.Double

GetDouble

BINARY_FLOAT

System.Single

GetFloat

BLOB

System.Byte[]

GetBytes

CHAR

System.String

System.Char[]

GetString

GetChars

CLOB

System.String

System.Char[]

GetString

GetChars

DATE

System.DateTime

GetDateTime

INTERVAL DAY TO SECOND

System.Timespan

GetTimeSpan

INTERVAL YEAR TO MONTH

System.Int64

GetInt64

LONG

System.String

System.Char[]

GetString

GetChars

LONG RAW

System.Byte[]

GetBytes

NCHAR

System.String

System.Char[]

GetString

GetChars

NCLOB

System.String

System.Char[]

GetString

GetChars

NUMBER

System.Decimal

System.Byte

System.Int16

System.Int32

System.Int64

System.Single

System.Double

GetDecimal

GetByte

GetInt16

GetInt32

GetInt64

GetFloat

GetDouble

NVARCHAR2

System.String

System.Char[]

GetString

GetChars

RAW

System.Byte[]

GetBytes

ROWID

System.String

System.Char[]

GetString

GetChars

TIMESTAMP

System.DateTime

GetDateTime

TIMESTAMP WITH LOCAL TIME ZONE

System.DateTime

GetDateTime

TIMESTAMP WITH TIME ZONE

System.DateTime

GetDateTime

UROWID

System.String

System.Char[]

GetString

GetChars

VARCHAR2

System.String

System.Char[]

GetString

GetChars

XMLType

System.String

System.Xml.XmlReader

GetString

GetXmlReader


Certain methods and properties of the OracleDataReader object require ODP.NET to map a NUMBER column to a .NET type based on the precision and scale of the column. These members are:

  • Item property

  • GetFieldType method

  • GetValue method

  • GetValues method

ODP.NET determines the appropriate .NET type by considering the following .NET types in order, and selecting the first .NET type from the list that can represent the entire range of values of the column:

  • System.Byte

  • System.Int16

  • System.Int32

  • System.Int64

  • System.Single

  • System.Double

  • System.Decimal

If no .NET type exists that can represent the entire range of values of the column, then an attempt is made to represent the column values as a System.Decimal type. If the value in the column cannot be represented as System.Decimal, then an exception is raised.

For example, consider two columns defined as NUMBER(4,0) and NUMBER(10,2). The first .NET types from the previous list that can represent the entire range of values of the columns are System.Int16 and System.Double, respectively. However, consider a column defined as NUMBER(20,10). In this case, there is no .NET type that can represent the entire range of values on the column, so an attempt is made to return values in the column as a System.Decimal type. If a value in the column cannot be represented as a System.Decimal type, then an exception is raised.

The Fill method of the OracleDataAdapter class uses the OracleDataReader object to populate or refresh a DataTable or DataSet with .NET types. As a result, the .NET type used to represent a NUMBER column in the DataTable or DataSet also depends on the precision and scale of the column.

ODP.NET Type Accessors

ODP.NET exposes provider-specific types that natively represent the data types in the database. In some cases, these ODP.NET types provide better performance and functioning than the corresponding .NET types. The ODP.NET types can be obtained from the OracleDataReader object by calling their respective typed accessor.

See Also:

"ODP.NET Types Overview" for a list of all ODP.NET types

Table 3-11 lists the valid type accessors that ODP.NET uses to obtain ODP.NET types for an Oracle native type.

Table 3-11 ODP.NET Type Accessors

Oracle Native Data Type ODP.NET Type Typed Accessor

BFILE

OracleBFile

GetOracleBFile

BINARY_DOUBLE

OracleDecimal

GetOracleDecimal

BINARY_FLOAT

OracleDecimal

GetOracleDecimal

BLOB

OracleBlob

OracleBlob

OracleBinary

GetOracleBlob

GetOracleBlobForUpdate

GetOracleBinary

CHAR

OracleString

GetOracleString

CLOB

OracleClob

OracleClob

OracleString

GetOracleClob

GetOracleClobForUpdate

GetOracleString

DATE

OracleDate

GetOracleDate

INTERVAL DAY TO SECOND

OracleIntervalDS

GetOracleIntervalDS

INTERVAL YEAR TO MONTH

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

ODP.NET fetches and caches rows from the database during the Read method invocations on the OracleDataReader object. The amount of LONG and LONG RAW column data that is retrieved from this operation is determined by InitialLONGFetchSize. The different behaviors observed when InitialLONGFetchSize is set to 0, greater than 0, and -1 are explained in the following sections.

Note:

ODP.NET does not support the CommandBehavior.SequentialAccess enumeration value. Therefore, LONG and LONG RAW data can be fetched randomly.

Setting InitialLONGFetchSize to Zero or a Value Greater than Zero

The specified amount of InitialLONGFetchSize characters or bytes for LONG or LONG RAW column data is retrieved into the cache during the Read method invocations on the OracleDataReader object.

By default, InitialLONGFetchSize is set to 0. In this case, ODP.NET does not fetch any LONG or LONG RAW column data during the Read method invocations on the OracleDataReader object. The LONG or LONG RAW data is fetched when the typed accessor method is explicitly invoked for the LONG or LONG RAW column, which incurs a database round-trip because no data is cached.

If InitialLONGFetchSize is set to a value greater than 0, that amount of specified data is cached by ODP.NET during the Read method invocations on the OracleDataReader object. If the application requests an amount of data less than or equal to the InitialLONGFetchSize through the typed accessor methods, no database round-trip is incurred. However, an additional database round-trip is required to fetch data beyond InitialLONGFetchSize.

To obtain data beyond the InitialLONGFetchSize characters or bytes, 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)

To be able to fetch the entire LONG or LONG RAW data without having a primary key column, a ROWID, or unique columns in the select list, set the size of the InitialLONGFetchSize property on the OracleCommand object to equal or greater than the number of characters or bytes needed to be retrieved.

The LONG or LONG RAW data is returned when the appropriate typed accessor method (GetChars, GetOracleString, or GetString for LONG or GetOracleBinary or GetBytes for LONG RAW) is called on the OracleDataReader object.

Setting InitialLONGFetchSize to -1

By setting InitialLONGFetchSize to -1, it is possible to fetch the entire LONG or LONG RAW data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list.

When InitialLONGFetchSize is set to -1, the entire LONG or LONG RAW data is retrieved and cached during Read method invocations on the OracleDataReader object. Calls to GetString, GetOracleString, GetChars, GetBytes, or GetOracleBinary in the OracleDataReader return the entire column data.

Obtaining LOB Data

ODP.NET fetches and caches rows from the database during the Read method invocations on the OracleDataReader object. The amount of LOB column data that is retrieved from this operation is determined by InitialLOBFetchSize.

The following sections explain the different behaviors observed when InitialLOBFetchSize is set to 0, greater than 0, and -1.

Setting InitialLOBFetchSize to Zero

By default, when the InitialLOBFetchSize property is 0, the GetOracleBlob and GetOracleClob methods can be invoked on the OracleDataReader object to obtain OracleBlob and OracleClob objects.

The following is a complete list of typed accessor methods that an application can call for the CLOB and BLOB columns, if InitialLOBFetchSize is set to 0:

  • Methods callable for BLOB column

    • GetBytes

    • GetValue

    • GetValues

    • GetOracleBinary

    • GetOracleBlob

    • GetOracleBlobForUpdate

    • GetOracleValue

    • GetOracleValues

  • Methods callable for CLOB column

    • GetChars

    • GetString

    • GetValue

    • GetValues

    • GetOracleString

    • GetOracleClob

    • GetOracleClobForUpdate

    • GetOracleValue

    • GetOracleValues

Setting InitialLOBFetchSize to a Value Greater than Zero

If InitialLOBFetchSize is set to a value greater than 0, ODP.NET caches LOB data up to InitialLOBFetchSize characters or bytes for each LOB selected during the Read method invocations on the OracleDataReader object.

This section discusses the ways to fetch beyond the InitialLOBFetchSize characters or bytes that are cached. The functionality has changed from Oracle Database 10g release 2 (10.2) and later.

Obtaining Additional Data Prior to Oracle Database 10g Release 2 (10.2)

With releases prior to Oracle Database 10g release 2 (10.2), obtaining data beyond InitialLOBFetchSize characters or bytes requires one of the following in the query 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 LOB data is fetched from the database when the appropriate typed accessor method is called on the OracleDataReader object.

To be able to fetch the entire LOB data without having a primary key column, a ROWID, or unique columns in the select list, set the size of the InitialLOBFetchSize property on the OracleCommand object to equal or greater than the number of characters or bytes needed to be retrieved.

When the InitialLOBFetchSize property is set to a nonzero value, the GetOracleBlob, GetOracleClob, GetOracleBlobForUpdate, and GetOracleClobForUpdate typed accessor methods are disabled.

Obtaining Additional Data From Oracle Database 10g Release 2 (10.2) and Later

Starting with Oracle Database 10g release 2 (10.2), the entire LOB data is returned when a typed accessor is invoked, regardless of the value set to the InitialLOBFetchSize property. Primary key, ROWID, or unique columns are not required to be in the query select list to obtain data beyond the specified InitialLOBFetchSize.

The GetOracleBlob, GetOracleClob, GetOracleBlobForUpdate, and GetOracleClobForUpdate methods can now be invoked even if InitialLOBFetchSize is greater than 0, starting with Oracle Database 10g release 2.

The following is a complete list of typed accessor methods that an application can call for the CLOB and BLOB columns if InitialLOBFetchSize is set to a value greater than 0:

  • Methods callable for BLOB column

    • GetBytes

    • GetValue

    • GetValues

    • GetOracleBinary

    • GetOracleBlob

    • GetOracleBlobForUpdate

    • GetOracleValue

    • GetOracleValues

  • Methods callable for CLOB column

    • GetChars

    • GetString

    • GetValue

    • GetValues

    • GetOracleString

    • GetOracleClob

    • GetOracleClobForUpdate

    • GetOracleValue

    • GetOracleValues

Setting InitialLOBFetchSize to -1

By setting InitialLOBFetchSize to -1, it is possible to fetch the entire LOB data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list. When InitialLOBFetchSize is set to -1, the entire LOB column data is fetched and cached during the Read method invocations on the OracleDataReader object. Calls to GetString, GetOracleString, GetChars, GetBytes, or GetOracleBinary in the OracleDataReader allow retrieving all data.

Methods Supported for InitialLOBFetchSize of -1

This section lists supported and not supported methods for the CLOB and BLOB data types when the InitialLOBFetchSize property is set to -1.

Table 3-12 lists supported and not supported methods for the CLOB data types.

Table 3-12 OracleDataReader CLOB Methods

Supported Not Supported

GetChars

GetOracleClob

GetString

GetOracleClobForUpdate

GetValue


GetValues


GetOracleString


GetOracleValue


GetOracleValues



Table 3-13 lists supported and not supported methods for the BLOB data types.

Table 3-13 OracleDataReader BLOB Methods

Supported Not Supported

GetBytes

GetOracleBlob

GetValue

GetOracleBlobForUpdate

GetValues


GetOracleBinary


GetOracleValue


GetOracleValues



Performance Considerations Related to the InitialLOBFetchSize Property

This section discusses the advantages and disadvantages of the various InitialLOBFetchSize property settings in different situations. It also discusses ways to enhance performance, depending on which database release you are using.

Prior to Oracle Database 10g Release 2 (10.2)

Setting the InitialLOBFetchSize property to a nonzero value can improve performance in certain cases. Using the InitialLOBFetchSize property can provide better performance than retrieving the underlying LOB data using OracleBlob or OracleClob objects. This is true if an application does not need to obtain OracleBlob and OracleClob objects from the OracleDataReader object and the size of the LOB column data is not very large. The InitialLOBFetchSize property is particularly useful in cases where the size of the LOB column data returned by the query is approximately the same for all the rows.

It is generally recommended that the InitialLOBFetchSize property be set to a value larger than the size of the LOB data for more than 80% of the rows returned by the query. For example, if the size of the LOB data is less than 1 KB in 80% of the rows, and more than 1 MB for 20% of the rows, set the InitialLOBFetchSize property to 1 KB.

Oracle Database 10g Release 2 (10.2) and Later

An application does not have to choose between performance and OracleBlob and OracleClob functionality. Setting the InitialLOBFetchSize property results in a performance boost and still gives the flexibility to use the OracleBlob and OracleClob objects.

If the size of the LOB data is unknown or if the LOB data size varies irregularly, then it is better to leave the InitialLOBFetchSize property to its default value of 0. This still gives better performance in most cases.

Setting the InitialLOBFetchSize property to a size equal to or greater than the LOB data size for most rows improves performance. It is generally recommended that the InitialLOBFetchSize property be set to a value larger than the size of the LOB data for more than 80% of the rows returned by the query. For example, if the size of the LOB data is less than 1 KB in 80% of the rows, and more than 1 MB for 20% of the rows, set the InitialLOBFetchSize property to 1 KB.

Controlling the Number of Rows Fetched in One Database Round-Trip

Application performance depends on the number of rows the application needs to fetch, and the number of database round-trips that are needed to retrieve them.

Use of FetchSize

The FetchSize property represents the total memory size in bytes that ODP.NET allocates to cache the data fetched from a database round-trip.

The FetchSize property can be set either on the OracleCommand or the OracleDataReader object, depending on the situation. Additionally, the FetchSize property of the OracleCommand object is inherited by the OracleDataReader object and can be modified.

If the FetchSize property is set on the OracleCommand object, then the newly created OracleDataReader object inherits the FetchSize property of the OracleCommand object. This inherited FetchSize value can be left as is, or modified to override the inherited value. The FetchSize property of the OracleDataReader object can be changed before the first Read method invocation, which allocates memory specified by the FetchSize property. All subsequent fetches from the database use the same cache allocated for that OracleDataReader object. Therefore, changing the FetchSize value after the first Read method invocation has no effect.

Fine-Tuning FetchSize

By fine-tuning the FetchSize property, applications can control memory usage and the number of rows fetched in one database round-trip for better performance. For example, if a query returns 100 rows and each row takes 1024 bytes, then setting the FetchSize property to 102400 takes just one database round-trip to fetch 100 rows. For the same query, if the FetchSize property is set to 10240, it takes 10 database round-trips to retrieve 100 rows. If the application requires all the rows to be fetched from the result set, the first scenario is faster than the second. However, if the application requires just the first 10 rows from the result set, the second scenario can perform better because it fetches only 10 rows, not 100 rows.

Using the RowSize Property

The RowSize property of the OracleCommand object is populated with the row size (in bytes) after an execution of a SELECT statement. The FetchSize property can then be set to a value relative to the RowSize property by setting it to the result of multiplying the RowSize value times the number of rows to fetch for each database round-trip.

For example, setting the FetchSize to RowSize * 10 forces the OracleDataReader object to fetch exactly 10 rows for each database round-trip. Note that the RowSize value does not change due to the data length in each individual column. Instead, the RowSize value is determined strictly from the metadata information of the database table(s) that the SELECT statement is executed against.

The RowSize property can be used to set the FetchSize property at design time or at runtime, as described in the following sections.

Setting FetchSize Value at Design Time

If the row size for a particular SELECT statement is already known from a previous execution, the FetchSize value of the OracleCommand object can be set at design time to the result of multiplying that row size times the number of rows the application wishes to fetch for each database round-trip. The FetchSize value set on the OracleCommand object is inherited by the OracleDataReader object that is created by the ExecuteReader method invocation on the OracleCommand object. Rather than setting the FetchSize value on the OracleCommand object, the FetchSize value can also be set on the OracleDataReader object directly. In either case, the FetchSize value is set at design time, without accessing the RowSize property value at runtime.

Setting FetchSize Value at Runtime

Applications that do not know the row size at design time can use the RowSize property of the OracleCommand object to set the FetchSize property of the OracleDataReader object. The RowSize property provides a dynamic way of setting the FetchSize property based on the size of a row.

After an OracleDataReader object is obtained by invoking the ExecuteReader method on the OracleCommand object, the RowSize property is populated with the size of the row (in bytes). By using the RowSize property, the application can dynamically set the FetchSize property of the OracleDataReader object to the product of the RowSize property value multiplied by the number of rows the application wishes to fetch for each database round-trip. In this scenario, the FetchSize property is set by accessing the RowSize property at runtime.