PL/SQL REF CURSOR and OracleRefCursor

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:

• Obtaining an OracleRefCursor

• Obtaining a REF CURSOR

• Populating an OracleDataReader from a REF CURSOR

• Populating the DataSet From a REF CURSOR

• Populating an OracleRefCursor From a REF CURSOR

• Updating a DataSet Obtained From a REF CURSOR

• Behavior of ExecuteScalar Method for REF CURSOR

Obtaining an OracleRefCursor

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.

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 a OracleDbType.RefCursor.

Populating an 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 multiple DataTables.

Populating an 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 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 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 database.

The OracleCommandBuilder cannot be used to generate SQL for REF CURSOR updates.

Behavior of 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.

See Also: Oracle Database Application Developer's Guide - Large Objects for more information