OracleCursor Class

Represents an Oracle REF CURSOR and value of cursor field.

For a list of all members of this type, see OracleCursor members.

Inheritance Hierarchy

System.Object
System.MarshalByRefObject
Devart.Data.Oracle.OracleCursor

Syntax

[Visual Basic]
Public Class OracleCursor 
   Inherits MarshalByRefObject
   Implements IDisposable

[C#]
public class OracleCursor : MarshalByRefObject, IDisposable

Remarks

You can get cursor as a parameter value by Devart.Common.DbParameterBase.Value property or by calling OracleDataReader.GetOracleCursor method of the OracleDataReader object.

You can use cursor to obtain OracleDataReader object and read data from it. The OracleDataReader can be retrieved only once.

Example

The following example uses OracleCursor class as the type of output parameter. It demonstrates how to retrieve Oracle REF CURSOR from server, obtain OracleDataReader object and read data from it. To create required stored procedure, table and fill it with data you can use the following script:

DROP TABLE DEPT;

CREATE TABLE DEPT (
  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

CREATE OR REPLACE PROCEDURE GET_DEPT(CUR OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN CUR FOR SELECT * FROM DEPT;
END;

[C#]

public void GettingCursor(string connStr) { 
    OracleConnection conn = new OracleConnection(connStr); 
    conn.Open(); 
    try { 
        OracleCommand command = conn.CreateCommand(); 
        command.CommandType = System.Data.CommandType.StoredProcedure; 
        command.CommandText = "GET_DEPT"; 
        command.Parameters.Add("cursor", OracleDbType.Cursor); 
        command.Parameters["cursor"].Direction = System.Data.ParameterDirection.Output; 
        command.ExecuteNonQuery(); 
        OracleDataReader reader = ((OracleCursor)command.Parameters["cursor"].Value).GetDataReader(); 
        while (reader.Read()) { 
            Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(reader.GetOrdinal("DName"))); 
        } 
    } 
    catch (OracleException ex) { 
        Console.WriteLine(ex.Message); 
    } 
    finally { 
        conn.Close(); 
    } 
}

[Visual Basic]

Public Sub GettingCursor(ByVal connStr As String)
     Dim conn As New OracleConnection(connStr)
     conn.Open()
     Try
         Dim command As OracleCommand = conn.CreateCommand()
         command.CommandType = System.Data.CommandType.StoredProcedure
         command.CommandText = "GET_DEPT"
         command.Parameters.Add("cursor", OracleDbType.Cursor)
         command.Parameters("cursor").Direction = System.Data.ParameterDirection.Output
         command.ExecuteNonQuery()
         Dim reader As OracleDataReader = (CType(command.Parameters("cursor").Value, OracleCursor)).GetDataReader()
         While reader.Read()
             Console.WriteLine(reader.GetInt32(0).ToString() + ", " + reader.GetString(reader.GetOrdinal("DName")))
         End While
     Catch ex As OracleException
         Console.WriteLine(ex.Message)
     Finally
         conn.Close()
     End Try
 End Sub

See Also

Language