OracleRefCursor Class

An OracleRefCursor object represents an Oracle REF CURSOR.

Class Inheritance

Object

  MarshalRefByObject

    OracleRefCursor

Declaration

// C#
public sealed class OracleRefCursor : MarshalRefByObject, IDisposable

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Example

// Database Setup
/*
connect scott/tiger@oracle 
CREATE OR REPLACE FUNCTION MyFunc(refcur_out OUT SYS_REFCURSOR) 
  RETURN SYS_REFCURSOR IS refcur_ret SYS_REFCURSOR;
BEGIN
  OPEN refcur_ret FOR SELECT * FROM EMP;
  OPEN refcur_out FOR SELECT * FROM DEPT;
  RETURN refcur_ret;
END MyFunc;
/
*/
 
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
class OracleRefCursorSample
{
  static void Main()
  {
    // Example demonstrates how to use REF CURSORs returned from 
    // PL/SQL Stored Procedures or Functions
    // Create the PL/SQL Function MyFunc as defined previously
    
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
      
    // Create an OracleCommand
    OracleCommand cmd = new OracleCommand("MyFunc", con);
    cmd.CommandType = CommandType.StoredProcedure;
      
    // Bind the parameters
    // p1 is the RETURN REF CURSOR bound to SELECT * FROM EMP;
    OracleParameter p1 = 
      cmd.Parameters.Add("refcur_ret", OracleDbType.RefCursor);
    p1.Direction = ParameterDirection.ReturnValue;
      
    // p2 is the OUT REF CURSOR bound to SELECT * FROM DEPT
    OracleParameter p2 = 
      cmd.Parameters.Add("refcur_out", OracleDbType.RefCursor);
    p2.Direction = ParameterDirection.Output;
      
    // Execute the command
    cmd.ExecuteNonQuery();
 
    // Construct an OracleDataReader from the REF CURSOR
    OracleDataReader reader1 = ((OracleRefCursor)p1.Value).GetDataReader();
 
    // Prints "reader1.GetName(0) = EMPNO"
    Console.WriteLine("reader1.GetName(0) = " + reader1.GetName(0));
 
    // Construct an OracleDataReader from the REF CURSOR
    OracleDataReader reader2 = ((OracleRefCursor)p2.Value).GetDataReader();
    
    // Prints "reader2.GetName(0) = DEPTNO"
    Console.WriteLine("reader2.GetName(0) = " + reader2.GetName(0));
 
    reader1.Close();
    reader1.Dispose();
 
    reader2.Close();
    reader2.Dispose();
 
    p1.Dispose();
    p2.Dispose();
 
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

Requirements

Namespace: Oracle.DataAccess.Types

Assembly: Oracle.DataAccess.dll

---

OracleRefCursor Members

OracleRefCursor members are listed in the following tables:

OracleRefCursor Static Methods

OracleRefCursor static methods are listed in Table 10-28.

OracleRefCursor Properties

OracleRefCursor properties are listed in Table 10-29.

OracleRefCursor Instance Methods

OracleRefCursor instance methods are listed in Table 10-30.

---

OracleRefCursor Static Methods

OracleRefCursor static methods are listed in Table 10-31.

---

OracleRefCursor Properties

OracleRefCursor properties are listed in Table 10-32.

Connection

This property refers to the OracleConnection used to fetch the REF CURSOR data.

Declaration

// C#
public OracleConnection Connection {get;}

Property Value

An OracleConnection.

Exceptions

ObjectDisposedException - The object is already disposed.

Remarks

This property is bound to a REF CURSOR once it is set. After the OracleRefCursor object is created by the constructor, this property is initially null. An OracleRefCursor object can be bound to a REF CURSOR after a command execution.

If the connection is closed or returned to the connection pool, the OracleRefCursor is placed in an uninitialized state and no operation can be carried out from it. However, the uninitialized OracleRefCursor can be reassigned to another REF CURSOR.

---

OracleRefCursor Instance Methods

OracleRefCursor instance methods are listed in Table 10-33.

Dispose

This instance method disposes of the resources allocated by the OracleRefCursor object.

Declaration

// C#
public void Dispose();

Implements

IDisposable

Remarks

The object cannot be reused after being disposed.

Once Dispose() is called, the object of OracleRefCursor is in an uninitialized state. Although some properties can still be accessed, their values may not be accountable. Since resources are freed, method calls can lead to exceptions.

GetDataReader

This instance method returns an OracleDataReader object for the REF CURSOR.

Declaration

// C#
public OracleDataReader GetDataReader();

Return Value

OracleDataReader

Remarks

Using the OracleDataReader, rows can be fetched from the REF CURSOR.