Table 3.10 provides
different values for OracleParameterStatus enumeration.
Table 3-10 OracleParameterStatus Members
For input parameters, it indicates that the
input value has been assigned to the column.
For output parameters, it indicates that the
provider assigned an intact value to the
Indicates that a
value has been fetched from a column or an
Indicates that a
value is to be inserted into a column.
Indicates that truncation has occurred when
fetching the data from the column.
PL/SQL REF CURSOR and
The REF CURSOR is a
datatype in the Oracle PL/SQL language. It represents a
cursor or a result set in the Oracle database server. The
OracleRefCursor is a corresponding ODP.NET type for the REF
There are no constructors
for OracleRefCursor objects. They can only be acquired as
parameter values from PL/SQL stored procedures, stored
functions, or anonymous blocks.
An OracleRefCursor is a
connected object. The connection used to execute the command
returning a OracleRefCursor object is required for its
lifetime. Once the connection associated with an
OracleRefCursor is closed, the OracleRefCursor cannot be
Obtaining a REF CURSOR
A REF CURSOR can be
obtained as an OracleDataReader, DataSet, or OracleRefCursor.
If the REF CURSOR is obtained as an OracleRefCursor object,
it can be used to create an OracleDataReader or populate a
DataSet from it. When accessing a REF CURSOR, always bind as
OracleDataReader from a REF CURSOR
An Oracle REF CURSOR can be
obtained as an OracleDataReader by calling the OracleCommand
ExecuteReader method. The output parameter with the
OracleDbType property set is bound to OracleDbType.RefCursor.
None of the output parameters of type OracleDbType.RefCursor
are populated after the ExecuteReader is invoked.
If there are multiple
output REF CURSOR parameters, use the OracleDataReader
NextResult method to access the next REF CURSOR. The
OracleDataReader NextResult method provides sequential
access to the REF CURSORs; only one REF CURSOR can be
accessed at a given time.
The order in which
OracleDataReader objects are created for the corresponding
REF CURSOR depends on the order in which the parameters are
bound. If a PL/SQL stored function returns a REF CURSOR,
then it becomes the first OracleDataReader and all the
output REF CURSOR objects follow the order in which the
parameters are bound.
Populating the DataSet
From a REF CURSOR
For the Fill method to
populate the DataSet properly, the SelectCommand of the
OracleDataAdapter must be bound with an output parameter of
type OracleDbType.RefCursor. If the Fill method is
successful, the DataSet is populated with a DataTable that
represents a REF CURSOR.
If the command execution
returns multiple REF CURSORs, the DataSet is populated with
OracleRefCursor From a REF CURSOR
When ExecuteNonQuery is
invoked on a command that returns one or more REF CURSORs,
each of the OracleCommand parameters that are bound as
OracleDbType.RefCursor gets a reference to an
To create an OracleDataReader from an OracleRefCursor
object, invoke GetDataReader from an OracleRefCursor object.
Subsequent calls to GetDataReader return the reference to
the same OracleDataReader.
To populate a DataSet with
an OracleRefCursor object, the application can invoke an
OracleDataAdapter Fill method that takes an OracleRefCursor
When multiple REF CURSORs are returned from a command
execution as OracleRefCursor objects, the application can
choose to create an OracleDataReader or populate a DataSet
with a particular OracleRefCursor object. All the
OracleDataReaders or DataSet created from the
OracleRefCursor are active at the same time and can be
accessed in any order.
Updating a DataSet
Obtained From a REF CURSOR
REF CURSORs are not
updatable. However, data that is retrieved into a DataSet
can be updated. Therefore, the OracleDataAdapter requires a
custom SQL statement to flush any REF CURSOR data updates to
The OracleCommandBuilder cannot be used to generate SQL for
REF CURSOR updates.
ExecuteScalar Method for REF CURSOR
ExecuteScalar returns the
return value of a stored function or the first bind
parameter of a stored procedure or an anonymous PL/SQL
block. Therefore, if the REF CURSOR is not the return value
of a stored function or the first bind parameter of a stored
procedure or an anonymous PL/SQL block, the REF CURSOR is
ignored by ExecuteScalar.
However, if the REF CURSOR
is a return value of a stored function or the first bind
parameter of a stored procedure or an anonymous PL/SQL
block, the value of the first column of the first row in the
REF CURSOR is returned.
ODP.NET provides an easy
and optimal way to access and manipulate large datatypes.
Oracle supports large character and large binary datatypes.
- CLOB - Character data
can store up to 4 gigabytes (4 GB).
- NCLOB - Unicode
National character set data can store up to 4 gigabytes.
Large Binary Datatypes
- BLOB - Unstructured
binary data can store up to 4 gigabytes.
- BFILE - Binary data
stored in external file can store up to 4 gigabytes.
ODP.NET provides three
objects for LOBs for manipulating LOB data: OracleBFile,
OracleBlob, and OracleClob.
Table 3.11 shows the proper
ODP.NET class to use for a particular Oracle LOB type.
Table 3-11 ODP.NET
Oracle LOB Type
ODP.NET LOB object
The ODP.NET LOB objects can
be obtained by calling the proper typed accessor on the
OracleDataReader or as an output parameter on a command
execution with the proper bind type.
All ODP.NET LOB objects
inherit from the .NET Stream class to provide generic Stream
operations. The LOB data (except for BFILEs) can be updated
using the ODP.NET LOB objects by using methods such as
Write. Data is not cached in the LOB objects when read and
write operations are carried out. Therefore, each Read or
Write request incurs a server round-trip. The OracleClob
overloads the Read method, providing two ways to read data
from a CLOB. The Read method that takes a byte as the
buffer populates it with CLOB data as Unicode byte array.
The Read method that takes a char as the buffer populates
it with Unicode characters.
Extensions can also be
found on the OracleBFile object. An OracleBFile object must
be explicitly opened using the OpenFile method before any
data can be read from it. To close a previously opened BFILE,
use the CloseFile method.
Every ODP.NET LOB object is
a connected object and requires a connection during its
lifetime. If the connection associated with a LOB object is
closed, then the LOB object is not usable and should be
If an ODP.NET LOB object is
obtained from an OracleDataReader through a typed accessor,
then its Connection property is set with a reference to the
same OracleConnection object used by the OracleDataReader.
If a LOB object is obtained as an output parameter, then its
Connection property is set with a reference to the same
OracleConnection property used by the OracleCommand. If a
LOB object is obtained by invoking an ODP.NET LOB object
constructor to create a temporary LOB, the Connection
property is set with a reference to the OracleConnection
object provided in the constructor.
The ODP.NET LOB object
Connection property is read-only and cannot be changed
during its lifetime. In addition, the ODP.NET LOB types
object can only be used within the context of the same
OracleConnection referenced by the ODP.NET LOB object. For
example, the ODP.NET LOB object's Connection must reference
the same connection as the OracleCommand if the ODP.NET LOB
object is a parameter of the OracleCommand. If that is not
the case, ODP.NET raises an exception when the command is
Temporary LOBs can be
instantiated for BLOBs, CLOBs, and NCLOBs. To instantiate an
ODP.NET LOB object that represents a temporary LOB, the
OracleClob or the OracleBlob constructor can be used.
Temporary ODP.NET LOB
objects can be used for the following purposes:
- To initialize and
populate a LOB column with empty or non-empty LOB data.
- To pass a LOB type as
an input parameter to a SQL statement, anonymous PL/SQL
blocks, or stored procedure.
- To act as the source
or the destination of data transfer between two LOB
objects as in the CopyTo operation.
ODP.NET XML Support
XML support in ODP.NET
provides the following features:
- Store XML data
natively in the database server as the Oracle database
native type, XMLType.
- Access relational and
object-relational data as XML data from an Oracle
database instance into Microsoft .NET environment,
process the XML using Microsoft .NET framework.
- Save changes to the
database server using XML data.
Rama Mohan G
Rama Mohan G.