dotConnect for Oracle Documentation
Devart.Data.Oracle Namespace / OracleCursor Class
Members Example

OracleCursor Class
Represents an Oracle REF CURSOR and value of cursor field.
Syntax
Remarks

You can get cursor as a parameter value by 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;
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();
        }
}
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
Inheritance Hierarchy

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

Requirements

Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

See Also