OracleRefCursor Class

An OracleRefCursor object represents an Oracle REF CURSOR.

Class Inheritance

System.Object

  System.MarshalRefByObject

    Oracle.DataAccess.Types.OracleRefCursor

Declaration

// C#
public sealed class OracleRefCursor : MarshalByRefObject, IDisposable, INullable

Thread Safety

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

Remarks

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

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

ODP.NET Version: ODP.NET for .NET Framework 2.0 or ODP.NET for .NET Framework 4

---

OracleRefCursor Members

OracleRefCursor members are listed in the following tables.

OracleRefCursor Static Methods

OracleRefCursor static methods are listed in Table 13-28.

OracleRefCursor Properties

OracleRefCursor properties are listed in Table 13-29.

OracleRefCursor Instance Methods

OracleRefCursor instance methods are listed in Table 13-30.

---

OracleRefCursor Static Methods

OracleRefCursor static methods are listed in Table 13-31.

---

OracleRefCursor Properties

OracleRefCursor properties are listed in Table 13-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.

FetchSize

This property specifies the size that the OracleDataReader internal cache needs to store result set data.

Declaration

// C#
public long FetchSize {get; set;}

Property Value

A long that specifies the size (in bytes) of the OracleRefCursor internal cache.

Exceptions

ArgumentException - The FetchSize value specified is invalid.

Remarks

Default = 131072.

The FetchSize property value is inherited by the OracleCommand that created the OracleRefCursor object. The FetchSize property on the OracleDataReader object determines the amount of data the OracleRefCursor fetches into its internal cache for each database round-trip.

This property is useful if the OracleRefCursor is explicitly used to fill the DataSet or DataTable through the OracleDataAdapter, because it can provide control on how the data of the REF CURSOR is fetched.

If an OracleDataReader object is created from the OracleRefCursor, the resulting OracleDataReader object inherits the FetchSize value of the OracleDataReader object. However, the inherited value can be overridden, if it is set before the first invocation of the OracleDataReader Read method for the given result set, by setting the OracleDataReader FetchSize property.

The RowSize and FetchSize properties handle UDT and XMLType data differently than other scalar data types. Because only a reference to the UDT and XMLType data is stored in the ODP.NET's internal cache, the RowSize property accounts for only the memory needed for the reference (which is very small) and not the actual size of the UDT and XMLType data. Thus, applications can inadvertently fetch a large number of UDT or XMLType instances from the database in a single database round-trip. This is because the actual size of UDT and XMLType data does not count against the FetchSize, and it would require numerous UDT and XMLType references to fill up the default cache size of 131072 bytes. Therefore, when fetching UDT or XMLType data, the FetchSize property must be appropriately configured to control the number of UDT and XMLType instances that are to be fetched, rather than the amount of the actual UDT and XMLType data to be fetched.

NOTE: For LOB and LONG data types, only the sizes specified in the InitialLOBFetchSize and InitialLONGFetchSize properties are accounted for by the RowSize property in addition to the metadata and reference information that is maintained by the cache for each LOB in the select list.

RowSize

This property specifies the amount of memory the OracleRefcursor internal cache needs to store one row of data.

Declaration

// C#
public long RowSize {get;}

Property Value

A long that indicates the amount of memory (in bytes) that an OracleRefcursor needs to store one row of data for the executed query.

Remarks

The RowSize property is set to a nonzero value when the OracleRefcursor object is created. This property can be used at design time or dynamically during run time, to set the FetchSize, based on number of rows. For example, to enable the OracleRefcursor to fetch N rows for each database round-trip, the OracleRefcursor FetchSize property can be set dynamically to RowSize * N. Note that for the FetchSize to take effect appropriately, it must be set before the it is used to fill the DataSet/DataTable using OracleDataAdapter.

If an OracleDataReader is obtained from the OracleRefCursor through the GetDataReader method, the resulting OracleDataReader will have its FetchSize property set to the FetchSize value of the OracleRefCursor.

---

OracleRefCursor Instance Methods

OracleRefCursor instance methods are listed in Table 13-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.