GetOracleRefCursor

This method returns the OracleRefCursor object corresponding to the REF CURSOR type column in the select list.

Declaration

// C#
public OracleRefCursor GetOracleRefCursor(int index);

Parameters

index

The zero-based column index.

Return Value

The OracleRefCursor object of the column.

Expections

InvalidOperationException - The connection is closed, the reader is closed, Read() has not been called, or all rows have been read.

IndexOutOfRangeException - The column index is invalid.

InvalidCastException - The accessor method is invalid for this column type or the column value is NULL.

Remarks

The application can get the OracleDataReader object from the returned OracleRefCursor object to read data.

The returned OracleRefCursor object inherits the FetchSize, InitialLOBFetchSize, InitialLONGFetchSize, and UseEdmMapping property values from OracleDataReader.

To minimize the number of open server cursors, OracleRefCursor objects should be explicitly disposed.

Sample

CREATE OR REPLACE FUNCTION refcurfunc1 RETURN SYS_REFCURSOR
AS
   VAR_REF SYS_REFCURSOR;
BEGIN
    OPEN VAR_REF FOR
        SELECT *
        FROM EMP;
    RETURN VAR_REF;
END;

using (OracleConnection con = new OracleConnection("user id = scott; password = <PASSWORD>; data source = oracle"))
{
  con.Open();
 
  OracleCommand cmd = con.CreateCommand();
  cmd.CommandText = "select refcurfunc1() from dual";
  OracleDataReader reader = cmd.ExecuteReader();
 
  while (reader.Read())
  {
    OracleRefCursor refCur = reader.GetOracleRefCursor(0);
    OracleDataReader refCurReader = refCur.GetDataReader();
    while (refCurReader.Read())
    {
      Console.WriteLine("RefCursor Data: " + refCurReader.GetString(0));
    }
    refCurReader.Close();
    refCurReader.Dispose();
    refCur.Dispose();
  }
}