Working with Oracle REF CURSORs
The .NET Framework Data Provider for Oracle supports the Oracle REF CURSOR data type. When using the data provider to work with Oracle REF CURSORs, you should consider the following behaviors.
Note
Some behaviors differ from those of the Microsoft OLE DB Provider for Oracle (MSDAORA).
For performance reasons, the Data Provider for Oracle does not automatically bind REF CURSOR data types, as MSDAORA does, unless you explicitly specify them.
The data provider does not support any ODBC escape sequences, including the {resultset} escape used to specify REF CURSOR parameters.
To execute a stored procedure that returns REF CURSORs, you must define the parameters in the OracleParameterCollection with an OracleType of Cursor and a Direction of Output. The data provider supports binding REF CURSORs as output parameters only. The provider does not support REF CURSORs as input parameters.
Obtaining an OracleDataReader from the parameter value is not supported. The values are of type DBNull after command execution.
The only CommandBehavior enumeration value that works with REF CURSORs (for example, when calling ExecuteReader) is CloseConnection; all others are ignored.
The order of REF CURSORs in the OracleDataReader depends on the order of the parameters in the OracleParameterCollection. The ParameterName property is ignored.
The PL/SQL TABLE data type is not supported. However, REF CURSORs are more efficient. If you must use a TABLE data type, use the OLE DB .NET Data Provider with MSDAORA.
In This Section
- REF CURSOR Examples
Contains three examples that demonstrate using REF CURSORs.
- Retrieving a REF CURSOR Parameter in an OracleDataReader
Demonstrates how to execute a PL/SQL stored procedure that returns a REF CURSOR parameter, and reads the value as an OracleDataReader.
- Retrieving Data from Multiple REF CURSORs Using an OracleDataReader
Demonstrates how to execute a PL/SQL stored procedure that returns two REF CURSOR parameters, and reads the values using an OracleDataReader.
- Filling a DataSet Using One or More REF CURSORs
Demonstrates how to execute a PL/SQL stored procedure that returns two REF CURSOR parameters, and fills a DataSet with the rows that are returned.