This article describes how to handle Oracle cursors in dotConnect Universal. This functionality is available for every supported Oracle provider.

• Introduction
• Using OUT Parameters
• Getting Multiple Cursors
• Using Stored Procedures
• Using Subquery as Cursors

Introduction

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 two ways:

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

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

Using OUT Parameters

A convenient 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 Universal allows to accomplish this task using UniCursor parameters.

For example,

...
string cmdText = "BEGIN OPEN :cur FOR SELECT * FROM dept; END;";
UniCommand cmd = new UniCommand(cmdText, myConnection);
cmd.Parameters.Add("cur", UniDbType.Cursor);
cmd.Parameters["cur"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
UniCursor myCursor = (UniCursor)cmd.Parameters["cur"].Value;
myDataAdapter.Fill(dataSet, "Table", myCursor);
...

...
Dim cmdText As string = "BEGIN OPEN :cur FOR SELECT * FROM dept; END;"
Dim cmd As UniCommand = new UniCommand(cmdText, myConnection)
cmd.Parameters.Add("cur", UniDbType.Cursor)
cmd.Parameters("cur").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim myCursor As UniCursor = CType(cmd.Parameters("cur").Value, UniCursor)
myDataAdapter.Fill(dataSet, "Table", myCursor)
...

Main feature of UniCursor class is that you can get UniDataReader object from it (using GetDataReader method). So UniCursor object can participate in fill operation. Note that UniDataAdapter cannot update DataSet filled in this way automatically using UniCommandBuilder because it is impossible to get schema table for this cursor. Thus you need to setup UniDataAdapter manually to be able to update DataSet.

Getting Multiple Cursors

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

...
string cmdText = "BEGIN OPEN :cur1 FOR SELECT * FROM dept; OPEN :cur2 FOR SELECT * FROM emp; end;";
UniCommand cmd = new UniCommand(cmdText, myConnection);
cmd.Parameters.Add("cur1", UniDbType.Cursor);
cmd.Parameters["cur1"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("cur2", UniDbType.Cursor);
cmd.Parameters["cur2"].Direction = ParameterDirection.Output;
myDataAdapter.SelectCommand = cmd;
myDataAdapter.Fill(dataSet);
...

...
Dim cmdText As string = "BEGIN OPEN :cur1 FOR SELECT * FROM dept; OPEN :cur2 FOR SELECT * FROM emp; end;"
Dim cmd As UniCommand = new UniCommand(cmdText, myConnection)
cmd.Parameters.Add("cur1", UniDbType.Cursor)
cmd.Parameters("cur1").Direction = ParameterDirection.Output
cmd.Parameters.Add("cur2", UniDbType.Cursor)
cmd.Parameters("cur2").Direction = ParameterDirection.Output
myDataAdapter.SelectCommand = cmd
myDataAdapter.Fill(dataSet)
...

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

Using Stored Procedures

The client-side PL/SQL block can be replaced with stored procedure on server. For example, the following script creates a package with the procedure that returns list of employees in a given department as cursor:

CREATE OR REPLACE PACKAGE SCOTT.EMPPKG as
  TYPE EmpCursor IS REF CURSOR;

  PROCEDURE SelectEmp(DeptNo IN NUMBER, Cur OUT EmpCursor);
END;
/

CREATE PACKAGE BODY SCOTT.EMPPKG IS

PROCEDURE SelectEmp(DeptNo IN NUMBER, Cur OUT EmpCursor)
  IS
 BEGIN
   OPEN Cur FOR
   SELECT * FROM emp
   WHERE DeptNo = DeptNumb;
 END;

END EMPPKG;

Given these database objects, client application can act like this:

...
UniConnection connection = new UniConnection(ConnStr);
connection.Open();
UniCommand cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "EMPPKG.SELECTEMP";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new UniParameter("DEPTNO", UniDbType.Int));
cmd.Parameters.Add(new UniParameter("CUR", UniDbType.Cursor));
cmd.Parameters["DEPTNO"].Value = 10;
cmd.Parameters["CUR"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
UniCursor uniCursor = (UniCursor)cmd.Parameters["Cur"].Value;

uniDataAdapter1.Fill(dsTest, "EmpCursor", uniCursor);
dataGrid1.DataSource = dsTest;
...

...
Dim connection As New UniConnection(ConnStr)
connection.Open()
Dim cmd As UniCommand = connection.CreateCommand()
connection.Open()
cmd.CommandText = "EMPPKG.SELECTEMP"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New UniParameter("DEPTNO", UniDbType.Int))
cmd.Parameters.Add(New UniParameter("CUR", UniDbType.Cursor))
cmd.Parameters("DEPTNO").Value = 10
cmd.Parameters("CUR").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim uniCursor1 As UniCursor = CType(cmd.Parameters("Cur").Value, UniCursor)

UniDataAdapter1.Fill(dsTest, "EmpCursor", uniCursor1)
dataGrid1.DataSource = dsTest
...

Using Subquery as Cursors

And there is one more way to use cursors, though this way works with dotConnect for Oracle only. Suppose there is a task to get employees for each department (see tables in the beginning of the article, they are in master-detail relationship). The most convenient way to accomplish task is to reconstruct relationship on client side. dotConnect Universal provides quite simple way to do this.

Here is the code cut:

...
string sql = "SELECT Dept.*, CURSOR(SELECT * FROM Scott.Emp WHERE Emp.DeptNo = Dept.DeptNo) AS " +
  "Emp FROM Scott.Dept";
UniCommand cmd = new UniCommand(sql, myConnection);
UniDataAdapter myDataAdapter = new UniDataAdapter();
myDataAdapter.SelectCommand = cmd;
myDataAdapter.Fill(dataSet, "Dept");
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 cmd As New UniCommand(sql, myConnection)
Dim myDataAdapter As New UniDataAdapter()
myDataAdapter.SelectCommand = cmd
myDataAdapter.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. UniDataAdapter adds one DataTable for main cursor and one for each column containing cursors. Additional columns are named in form "<main table name>.<main table name><cursor column name>". During fetch UniDataAdapter 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 UniDataAdapter can update one table at a time. So you need to set up additional UniDataAdapter to update "Dept.DeptEmp" DataTable.

See Also

UniCursor Class  | UniParameter Class  | Devart.Data.Universal Namespace