Listing 1
CONNECT SCOTT/TIGER

CREATE OR REPLACE PACKAGE SCOTT.MYCURSOR AS
  TYPE CURTYPE IS REF CURSOR ;
END MYCURSOR;

/

GRANT EXECUTE ON SCOTT.MYCURSOR TO PUBLIC;


Listing 2
CONNECT SCOTT/TIGER

CREATE OR REPLACE PROCEDURE SCOTT.EMP_BY_DEPT(
  P_DEPTNO    IN      INTEGER,
  P_RESULTSET IN OUT  SCOTT.MYCURSOR.CURTYPE
  )
AS
  -- Any declarations here
BEGIN

  -- Any statements here

  -- Open cursor
  OPEN P_RESULTSET FOR
    SELECT EMPNO,
           ENAME,
           JOB,
           (SELECT ENAME
              FROM SCOTT.EMP B
             WHERE B.EMPNO = A.MGR) AS MNAME,
           MGR,
           HIREDATE,
           SAL,
           COMM,
           DEPTNO
      FROM SCOTT.EMP A
     WHERE DEPTNO = P_DEPTNO;
END;

/

GRANT EXECUTE ON SCOTT.EMP_BY_DEPT TO PUBLIC;


Listing 3

// Set SQL query to fetch departments
string sqlQuery =
  "select deptno, dname, loc from scott.dept;";

// Populate dataset
using (OracleConnection connection = new OracleConnection(m_ConnectionString))
{
    OracleCommand command = new OracleCommand(sqlQuery, connection);
    OracleDataAdapter dataAdapter = new OracleDataAdapter(command);
    dataAdapter.Fill(m_DeptDS);
}

// Populate combo-box
this.comboBox1.DisplayMember = "dname";
this.comboBox1.ValueMember = "deptno";
this.comboBox1.DataSource = this.m_DeptDS.Tables[0];


Listing 4

private void Retrieve()
{
    // Set current dataobject
    this.dw1.LibraryList = "demo.pbl";
    this.dw1.DataWindowObject = "d_emp_by_dept";

    // Get retrieval arguments
    int DeptNo = Int32.Parse(this.comboBox1.SelectedValue.ToString());
    object[] ArgsList = new object[] { DeptNo };

    // Retrieve
    try
    {
        // Connect to database
        this.m_Trans.Connect();

        // Set transaction object to datawindow control
        this.dw1.SetTransaction(this.m_Trans);

        // Retrieve data
        int count = this.dw1.Retrieve(ArgsList);
    }
    catch (OracleException ex)
    {
        _ShowError(ex.Message,
                   "Oracle Exception");
    }
    catch (DbErrorException ex)
    {
            _ShowError(ex.SqlErrorText,
                   "DbError Exception");
    }
    catch (TransactionException ex)
    {
        _ShowError(ex.Message,
                   "Transaction Exception");
    }
    catch (Exception ex)
    {
        _ShowError(ex.ToString(),
                   "Exception");
    }
    finally
    {
        if (this.m_Trans.IsConnected)
            this.m_Trans.Disconnect();
    }
}