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 are listed in the following tables:
OracleRefCursor Static Methods
OracleRefCursor
static methods are listed in Table 10-28.
Table 10-28 OracleRefCursor Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleRefCursor Properties
OracleRefCursor
properties are listed in Table 10-29.
Table 10-29 OracleRefCursor Properties
Properties | Description |
---|---|
Connection | A reference to the OracleConnection used to fetch the REF CURSOR data |
OracleRefCursor Instance Methods
OracleRefCursor
instance methods are listed in Table 10-30.
Table 10-30 OracleRefCursor Instance Methods
Methods | Description |
---|---|
Dispose | Disposes the resources allocated by the OracleRefCursor object |
Equals |
Inherited from Object (Overloaded) |
GetDataReader | Returns an OracleDataReader object for the REF CURSOR |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
OracleRefCursor
static methods are listed in Table 10-31.
Table 10-31 OracleRefCursor Static Methods
Methods | Description |
---|---|
Equals |
Inherited from Object (Overloaded) |
OracleRefCursor
properties are listed in Table 10-32.
Table 10-32 OracleRefCursor Properties
Properties | Description |
---|---|
Connection | A reference to the OracleConnection used to fetch the REF CURSOR data |
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 are listed in Table 10-33.
Table 10-33 OracleRefCursor Instance Methods
Methods | Description |
---|---|
Dispose | Disposes the resources allocated by the OracleRefCursor object |
Equals |
Inherited from Object (Overloaded) |
GetDataReader | Returns an OracleDataReader object for the REF CURSOR |
GetHashCode |
Inherited from Object |
GetType |
Inherited from Object |
ToString |
Inherited from Object |
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.
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
.