January 20, 2012

Populating OracleDataReader From REF CURSOR

A REF CURSOR data type can be obtained as an OracleDataReader object by calling theExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor. None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.

If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR data type. The OracleDataReader NextResult method provides sequential access to the REF CURSOR data types; only one REF CURSOR data type can be accessed at a given time.

The order in which OracleDataReader objects are created for the corresponding REF CURSOR data types depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR data type, then it becomes the first OracleDataReader object and all the output REF CURSOR data types follow the order in which the parameters are bound.

The following sample code will illustrate the usage.
using(OracleConnection conn = new OracleConnection(@"DataSource = source; User Id = username;Password = password;"))
          OracleCommand cmd = new OracleCommand();
          cmd.Connection = conn;
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "StoredProcedureName";
          cmd.Parameters.Add("REF_CURSOR_SAMPLE", OracleType.RefCursor).Direction =
          OracleDataReader reader = cmd.ExecuteReader();
              while (oracleDataReader.Read())
                     for (int i = 0; i < oracleDataReader.FieldCount; i++)
                              Console.WriteLine("Value is {0}", oracleDataReader.GetValue(i));
        }while (oracleDataReader.NextResult());


  1. Ayuda, quiero retornar un cursor y un varchar, como puedo hacer eso

    1. Can you post your question in detail?