Obtaining Data from an OracleDataReader Object

.NET Type Accessors

Table 3-12 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.

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.

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

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.

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.

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.

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.

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 on the OracleCommand, OracleDataReader, or OracleRefCursor object, depending on the situation. It controls the fetch size for filling a DataSet or DataTable using an OracleDataAdapter.

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. When the next 10 rows are fetched, then the memory allocated for rows 1-10 is reused for rows 11-20.

The larger the FetchSize, the more system memory is used. Developers should not set large fetch sizes if their client systems have limited memory resources.

Using the RowSize Property

The RowSize property of the OracleCommand or OracleRefCursor 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 run time, as described in the following sections.