Working with Cursors

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 cursor in three ways:

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

Following section describes last two ways of working with cursor.

For further explanations consider these table definitions. 

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
);

Suppose there is a task to get employees for each department. Above tables are in master/detail relationship. The most convenient way to accomplish task is to reconstruct relationship on client side. dotConnect for Oracle provides quite simple way to do this.

Here is the code cut:

[C#]

...
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.DataMember = "Dept";
detailGrid.DataMember = "Dept.DeptEmp";
...

[Visual Basic]

...
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.DataMember = "Dept"
detailGrid.DataMember = "Dept.DeptEmp"
...

First, pay attention to SQL text. In select list there is a sub-query with result set cast to CURSOR type. That is the way you should write SQL statement to get all the employees for each department in a single query.

Second, that is important how DataSet is filled. OracleDataAdapter adds one DataTable for main cursor and one for each column containing cursors. Additional tables are named in form "

.
". During fetch OracleDataAdapter fills tables creating relation between "Dept" and "Dept.DeptEmp". This relation works like foreign key which allows DataSet component to filter "Dept.DeptEmp" DataTable when you are navigating main "Dept" DataTable.

Third, there is a notion concerning update of the DataSet. In the given example you can only update 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 cursor from Oracle server is to use PL/SQL REF CURSOR type. Often it is required to do some additional server-side actions when you execute query. If it is impossible to put them all into one SELECT statement then you write PL/SQL block or stored procedure. But it is still required to get record set from server. dotConnect for Oracle allows you to accomplish this task using OracleCursor parameters.

For example,

[C#]

...
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);
...

[Visual Basic]

...
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)
...

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

In addition, dotConnect for Oracle allows you to work with multiple cursors. It is much like above sample, but you can also iterate through result sets using OracleDataReader.NextResult method. Following code shows how to fill DataSet.

[C#]

...
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);
...

[Visual Basic]

...
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.