Oracle® Data Provider for .NET Developer's Guide 10g Release 1 (10.1) Part Number B10117-01 |
|
|
View PDF |
The REF
CURSOR
is a datatype in the Oracle PL/SQL language. It represents a cursor or a result set in the Oracle database. The OracleRefCursor
is a corresponding ODP.NET type for the REF
CURSOR
type.
This section discusses the following aspects of using REF
CURSOR
and OracleRefCursor
objects:
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 used.
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 a OracleDbType.RefCursor
.
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
CURSOR
s; 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.
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
CURSOR
s, the DataSet
is populated with multiple DataTable
s.
When ExecuteNonQuery
is invoked on a command that returns one or more REF
CURSOR
s, each of the OracleCommand
parameters that are bound as OracleDbType.RefCursor
gets a reference to an OracleRefCursor
object.
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
object.
When multiple REF
CURSOR
s 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 OracleDataReader
s or DataSet
created from the OracleRefCursor
are active at the same time and can be accessed in any order.
REF
CURSOR
s 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 database.
The OracleCommandBuilder
cannot be used to generate SQL for REF
CURSOR
updates.
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.