Obtaining LOB Data
When an OracleDataReader is
created containing LOB column types, OracleDataReader
determines whether the LOB 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,
InitialLOBFetchSize is set to 0. If the InitialLOBFetchSize
property value of the OracleCommand is left as 0, the entire
LOB data retrieval is deferred until that data is explicitly
requested by the application. If the InitialLOBFetchSize
property is set to a nonzero value, the LOB data is
immediately fetched up to the number of characters or bytes
that the InitialLOBFetchSize property specifies.
By default, when
InitialLOBFetchSize property is 0, GetOracleBlob() and
GetOracleClob() can be invoked on the OracleDataReader to
obtain OracleBlob and OracleClob objects. However, if the
InitialLOBFetchSize is set to a nonzero value, GetOracleBlob()
and GetOracleClob() methods are disabled. In this scenario,
the BLOB and CLOB data needs to be fetched by using GetBytes()
and GetChars(), respectively.
to a nondefault value can improve performance in certain
cases. Using InitialLOBFetchSize in conjunction with
GetBytes() and GetChars() can provide better performance
than retrieving the underlying LOB data using OracleBlob or
OracleClob objects in the following situation: If an
application does not need to obtain OracleBlob and
OracleClob objects from the OracleDataReader and the size
the LOB column data is not very large. InitialLOBFetchSize
is particularly useful in cases where the size of the LOB
column data returned by query is approximately the same for
all the rows.
It is generally recommended
that InitialLOBFetchSize 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 1KB in 80% of the rows and more than 1MB for 20%
of the rows, set InitialLOBFetchSize to 1KB.
ODP.NET does not support
CommandBehavior.SequentialAccess. Therefore, LOB data can be
fetched in a random fashion.
To obtain data beyond
InitialLOBFetchSize bytes or characters, one of the
following must be in the select list:
• primary key
• 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 is called on the OracleDataReader object.
Note that the primary key column is not required if
InitialLOBFetchSize is set to 0.
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 InitialLOBFetchSize property on
the OracleCommand object to an amount equal to or greater
than the bytes or characters that need to be retrieved.
Controlling the Number
of Rows Fetched in One Server Round-Trip
depends on the number of rows the application needs to fetch
and the number of database round-trips that are needed to
Use of FetchSize
The FetchSize property
represents the total memory size in bytes that ODP.NET
allocates to cache the data fetched from a server
The FetchSize property can
be set either on the OracleCommand or the OracleDataReader
depending on the situation. Additionally, the FetchSize
property of the OracleCommand is inherited by the
OracleDataReader and can be modified.
If the FetchSize property
is set on the OracleCommand, then the newly created
OracleDataReader inherits the FetchSize property of the
OracleCommand. This inherited FetchSize 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. All subsequent fetches
from the database use the same cache allocated for that
OracleDataReader. Therefore, changing the FetchSize after
the first Read method invocation has no effect.
By fine-tuning the
FetchSize property, applications can control memory usage
and the number of rows fetched in one server round-trip for
better performance. For example, if a query returns 100 rows
and each row takes 1024 bytes, then setting FetchSize to
102400 takes just one server round-trip to fetch the hundred
rows. For the same query, if the FetchSize is set to 10240,
it takes 10 server 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
since it only fetches 10 rows and not 100 rows.
Using the RowSize
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 by setting it to the product of RowSize and the
number of rows to fetch for each server round-trip.
For example, setting the
FetchSize to RowSize * 10 forces the OracleDataReader to
fetch exactly 10 rows for each server round-trip. Note that
the RowSize does not change due to the data length in each
individual columns. Instead, the RowSize is determined
strictly from the metadata information of the database
table(s) that the SELECT is executed against.
The RowSize property can be
used to set the FetchSize 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, FetchSize of the OracleCommand can be set at
design time to the product of that row size and the number
of rows the application wishes to fetch for each server
round-trip. The FetchSize value set on the OracleCommand
object is inherited by the OracleDataReader that is created
by the ExecuteReader method invocation on the OracleCommand.
Rather than setting the FetchSize on the OracleCommand, the
FetchSize can also be set on the OracleDataReader directly.
In either case, the FetchSize 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, 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 to the product of the
RowSize property value and the number of rows the
application wishes to fetch for each server round-trip. In
this scenario, the FetchSize is set by accessing the RowSize
property at runtime.
The OracleCommand object
represents SQL statements or stored procedures executed on
the Oracle Database.
The Oracle Database starts
a transaction only in the context of a connection. Once a
transaction starts, all the successive command execution on
that connection run in the context of that transaction.
Transactions can only be started on a OracleConnection
object and the read-only Transaction property on the
OracleCommand object is implicitly set by the
OracleConnection object. Therefore, the application cannot
set the Transaction property, nor does it need to.
ODP.NET allows applications
to retrieve data as either a .NET Framework type or an
How the data is retrieved
depends on whether application sets the OUT parameter to the
DbType property (.NET type) or OracleDbType property (ODP.NET
type) of the OracleParameter.
For example, if the output
parameter is bound as an DbType.String, the output data is
returned as a .NET String. On the other hand, if the
parameter is bound as OracleDbType.Char, the output data is
returned as OracleString type.
When the DbType of an
OracleParameter is set, the OracleDbType of the
OracleParameter changes accordingly, and vice versa. The
parameter set last prevails.
Lastly, an application can simply bind the data and have
ODP.NET infer both the DbType and OracleDbType from the .NET
type of the parameter value.
InputOutput, Output, and ReturnValue parameters with the
Oracle data, through the execution of the following
An application should not
bind a value for output parameters; it is the responsibility
of ODP.NET to create the value object and populate the
OracleParameter Value property with the object.
Rama Mohan G
Rama Mohan G.