An OracleRefCursor object represents an Oracle REF CURSOR.
System.Object
System.MarshalRefByObject
Oracle.DataAccess.Types.OracleRefCursor
// C# public sealed class OracleRefCursor : MarshalByRefObject, IDisposable, INullable
All public static methods are thread-safe, although instance methods do not guarantee thread safety.
To minimize the number of open server cursors, OracleRefReader objects should be explicitly disposed.
// 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();
}
}
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
See Also:
OracleRefCursor members are listed in the following tables.
OracleRefCursor Static Methods
OracleRefCursor static methods are listed in Table 13-28.
Table 13-28 OracleRefCursor Static Methods
| Methods | Description |
|---|---|
|
|
Inherited from |
OracleRefCursor properties are listed in Table 13-29.
Table 13-29 OracleRefCursor Properties
| Properties | Description |
|---|---|
|
A reference to the |
|
|
Specifies the size that the |
|
|
Specifies the amount of memory the |
OracleRefCursor Instance Methods
OracleRefCursor instance methods are listed in Table 13-30.
Table 13-30 OracleRefCursor Instance Methods
| Methods | Description |
|---|---|
|
Disposes the resources allocated by the |
|
|
|
Inherited from |
|
Returns an |
|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
OracleRefCursor static methods are listed in Table 13-31.
OracleRefCursor properties are listed in Table 13-32.
Table 13-32 OracleRefCursor Properties
| Properties | Description |
|---|---|
|
A reference to the |
|
|
Specifies the size that the |
|
|
Specifies the amount of memory the |
This property refers to the OracleConnection used to fetch the REF CURSOR data.
// C#
public OracleConnection Connection {get;}
An OracleConnection.
ObjectDisposedException - The object is already disposed.
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.
This property specifies the size that the OracleDataReader internal cache needs to store result set data.
// C#
public long FetchSize {get; set;}
A long that specifies the size (in bytes) of the OracleRefCursor internal cache.
ArgumentException - The FetchSize value specified is invalid.
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.
This property specifies the amount of memory the OracleRefcursor internal cache needs to store one row of data.
// C#
public long RowSize {get;}
A long that indicates the amount of memory (in bytes) that an OracleRefcursor needs to store one row of data for the executed query.
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 are listed in Table 13-33.
Table 13-33 OracleRefCursor Instance Methods
| Methods | Description |
|---|---|
|
Disposes the resources allocated by the |
|
|
|
Inherited from |
|
Returns an |
|
|
|
Inherited from |
|
|
Inherited from |
|
|
Inherited from |
This instance method disposes of the resources allocated by the OracleRefCursor object.
// C# public void Dispose();
IDisposable
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.