A cursor identifies the result set produced by query execution, which consists of all the rows that meet the query search criteria. Oracle server can return a cursor in three ways:

• by executing an ordinary SELECT statement;
• by executing a SELECT statement with cursor fields;
• by returning PL/SQL REF CURSOR as an output parameter.

The following section describes the last two ways of working with cursors.

In our examples we will use the followind database tables:

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

CREATE TABLE EMP (
  EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

These tables store information about departments and their employees. For example, we need to get employees for each department. In order to demontrate the use of OracleCursor class, we will do it in the following way:

...
string sql = "SELECT Dept.*, CURSOR(SELECT * " +
   "FROM Scott.Emp WHERE Emp.DeptNo = Dept.DeptNo) AS Emp FROM Scott.Dept";
OracleCommand oraCommand = new OracleCommand(sql, oraConnection);
OracleDataAdapter oraDataAdapter = new OracleDataAdapter();
oraDataAdapter.SelectCommand = oraCommand;
oraDataAdapter.Fill(dataSet, "Dept");
masterGrid.DataSource = dataSet1;
detailGrid.DataSource = dataSet1;
masterGrid.DataMember = "Dept";
detailGrid.DataMember = "Dept.DeptEmp";
...

...
Dim sql As string = "SELECT Dept.*, CURSOR(SELECT &_
   * FROM Scott.Emp WHERE Emp.DeptNo = Dept.DeptNo) AS " + _
  "Emp FROM Scott.Dept"
Dim oraCommand OracleCommand = new OracleCommand(sql, oraConnection)
Dim oraDataAdapter OracleDataAdapter = new OracleDataAdapter()
oraDataAdapter.SelectCommand = oraCommand
oraDataAdapter.Fill(dataSet, "Dept")
masterGrid.DataSource = dataSet1
detailGrid.DataSource = dataSet1
masterGrid.DataMember = "Dept"
detailGrid.DataMember = "Dept.DeptEmp"
...

First, note the SQL text. In select list, there is a sub-query with the result set cast to the CURSOR type.

Second, it is important how DataSet is filled. OracleDataAdapter adds one DataTable for the main cursor and one for each column containing cursors. Additional tables are named in the following way: "<main table name>.<main table name><cursor column name>". During fetch OracleDataAdapter fills tables creating relation between "Dept" and "Dept.DeptEmp". This relation works like a foreign key, which allows the DataSet component to filter "Dept.DeptEmp" DataTable when you are navigating the main "Dept" DataTable.

Third, there is a notion concerning update of the DataSet. In the given example you can only update the main DataTable because OracleDataAdapter can update one table at a time. So you need to set up additional OracleDataAdapter to update "Dept.DeptEmp" DataTable.

Another way to get a cursor from an Oracle server is to use the PL/SQL REF CURSOR type. Sometimes you need to perform some additional server-side actions when executing a query. If it is impossible to put them all into a single SELECT statement, a PL/SQL block or stored procedure can be used. But it is still required to get a record set from the server. dotConnect for Oracle allows you to retrieve this record set using OracleCursor parameters.

For example,

...
string cmdText = "begin open :cur for select * from dept; end;";
OracleCommand oraCommand = new OracleCommand(cmdText, oraConnection);
oraCommand.Parameters.Add("cur", OracleDbType.Cursor);
oraCommand.Parameters["cur"].Direction = ParameterDirection.Output;
oraCommand.ExecuteNonQuery();
OracleCursor oraCursor = (OracleCursor)oraCommand.Parameters["cur"].Value;
oraDataAdapter.Fill(dataSet, "Table", oraCursor);
...

...
Dim cmdText As string = "begin open :cur for select * from dept; end;"
Dim oraCommand As OracleCommand = new OracleCommand(cmdText, oraConnection)
oraCommand.Parameters.Add("cur", OracleDbType.Cursor)
oraCommand.Parameters("cur").Direction = ParameterDirection.Output
oraCommand.ExecuteNonQuery()
Dim oraCursor As OracleCursor = CType(oraCommand.Parameters("cur").Value, OracleCursor)
oraDataAdapter.Fill(dataSet, "Table", oraCursor)
...

The main feature of the OracleCursor class is that you can get an OracleDataReader object from it. So an OracleCursor object can participate in the fill operation. Note that OracleDataAdapter cannot update DataSet filled in this way automatically using OracleCommandBuilder because it is impossible to get schema table for the cursor. Thus you need to configure OracleDataAdapter manually to be able to update DataSet.

dotConnect for Oracle allows you to work with multiple cursors. It is mostly similar to the above sample, but you can also iterate through result sets using the OracleDataReader.NextResult method. The following code shows how to fill DataSet:

...
string cmdText = "begin open :cur1 for select * from dept;" + 
    "open :cur2 for select * from emp; end;";
OracleCommand oraCommand = new OracleCommand(cmdText, oraConnection);
oraCommand.Parameters.Add("cur1", OracleDbType.Cursor);
oraCommand.Parameters["cur1"].Direction = ParameterDirection.Output;
oraCommand.Parameters.Add("cur2", OracleDbType.Cursor);
oraCommand.Parameters["cur2"].Direction = ParameterDirection.Output;
oraDataAdapter.SelectCommand = oraCommand;
oraDataAdapter.Fill(dataSet);
...

...
Dim cmdText As string = "begin open :cur1 for select * from dept; &_
    open :cur2 for select * from emp; end;"
Dim oraCommand As OracleCommand = new OracleCommand(cmdText, oraConnection)
oraCommand.Parameters.Add("cur1", OracleDbType.Cursor)
oraCommand.Parameters("cur1").Direction = ParameterDirection.Output
oraCommand.Parameters.Add("cur2", OracleDbType.Cursor)
oraCommand.Parameters("cur2").Direction = ParameterDirection.Output
oraDataAdapter.SelectCommand = oraCommand
oraDataAdapter.Fill(dataSet)
...

As for update, you should manually setup OracleDataAdapter for each record set you'd like to update as it was said earlier.

Additionally dotConnect for Oracle supports returning implicit result sets on Oracle 12c. Implicit result sets are supported only in OCI mode with Oracle Client 12c. If a stored procedure returns both implicit result sets and output cursor parameters, the result sets of output cursor parameters are returned first, and then implicit result sets are returned.

See Also

OracleCursor Class  | Devart.Data.Oracle Namespace