Microsoft KB Archive/321715

= How To Retrieve Multiple Ref_cursors from an Oracle Stored Procedure by Using the .NET Managed Provider For Oracle =

Article ID: 321715

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Studio .NET 2002 Professional Edition
 * Microsoft Visual Studio .NET 2003 Professional Edition

-



This article was previously published under Q321715



IN THIS TASK
SUMMARY
 * Requirements
 * Create the Oracle Tables
 * Create the Oracle Packages
 * Create the Visual Basic .NET Application

REFERENCES



SUMMARY
This step-by-step article describes how to use the .NET Data Provider for Oracle to retrieve multiple Ref Cursors from an Oracle stored procedure and how to use the Relations property of the Dataset object to build a relationship between the two tables that are returned by the Ref Cursors.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
 * Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP Professional
 * Microsoft Visual Studio .NET
 * Oracle Client 8.1.7 or later
 * .NET Data Provider for Oracle (you can download this version from the Microsoft Download Center)

This article assumes that you are familiar with the following topics:
 * Microsoft Visual Basic .NET
 * ADO.NET fundamentals and syntax

back to the top

Create the Oracle Tables
This sample uses tables that are defined in the Oracle Scott/Tiger schema. By default, the Oracle Scott/Tiger schema is included with a standard Oracle installation.

If this schema does not exist, you must run the following table and insert scripts for the tables: CREATE TABLE DEPT (DEPTNO NUMBER(2,0) NOT NULL,   DNAME VARCHAR2(14) NULL,    LOC VARCHAR2(13) NULL,   PRIMARY KEY (DEPTNO)   );

INSERT INTO Dept VALUES(11,'Sales','Texas'); INSERT INTO Dept VALUES(22,'Accounting','Washington'); INSERT INTO Dept VALUES(33,'Finance','Maine');

CREATE TABLE EMP (EMPNO NUMBER(4,0) NOT NULL,   ENAME VARCHAR2(10) NULL,    JOB VARCHAR2(9) NULL,    MGR NUMBER(4,0) NULL,    HIREDATE DATE,   SAL NUMBER(7,2) NULL,    COMM NUMBER(7,2) NULL,    DEPTNO NUMBER(2,0) NULL,   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),     PRIMARY KEY (EMPNO)   );

INSERT INTO Emp VALUES(123,'Bob','Sales',555,sysdate,35000,12,11); INSERT INTO Emp VALUES(321,'Sue','Finance',555,sysdate,42000,12,33); INSERT INTO Emp VALUES(234,'Mary','Account',555,sysdate,33000,12,22); back to the top

Create the Oracle Packages
Create the following Oracle package on the Oracle server: CREATE OR REPLACE PACKAGE MultiRefCursors AS        TYPE Emp_cursor IS REF CURSOR ; TYPE Dept_cursor IS REF CURSOR ; Procedure EmpDept (io_cursor1 IN OUT Emp_cursor, io_cursor2 IN OUT Dept_cursor); END MultiRefCursors; / Create the following Oracle package body on the Oracle server: CREATE OR REPLACE PACKAGE BODY MultiRefCursors AS  Procedure EmpDept (io_cursor1 IN OUT Emp_cursor, io_cursor2 IN OUT Dept_cursor) IS        E_cursor Emp_cursor; D_Cursor Dept_Cursor; BEGIN OPEN E_cursor FOR SELECT * from Emp;

OPEN D_cursor FOR SELECT * from Dept;

io_cursor1 := E_cursor; io_cursor2 := D_cursor; END EmpDept; END MultiRefCursors; / back to the top

Create the Visual Basic .NET Application
 Create a new Visual Basic Windows Application project. By default, Form1 is added to the project. On the Project menu, click Add Reference, and then set a reference for System.Data.OracleClient. Add a command button and a Datagrid object to the form.  Add the following code at the top of the Code window: Imports System.Data.OracleClient   Add the following code to the Button1_Click event of Form1: Dim Ds As New DataSet Dim Oraclecon As New OracleConnection(&quot;Data Source=YourOracle;User ID=scott;Password=tiger&quot;) Oraclecon.Open

Dim myCMD As New OracleCommand myCMD.Connection = Oraclecon myCMD.CommandText = &quot;multiRefCursors.EmpDept&quot; myCMD.CommandType = CommandType.StoredProcedure myCMD.Parameters.Add(New OracleParameter(&quot;io_cursor1&quot;, OracleType.Cursor)).Direction = ParameterDirection.Output myCMD.Parameters.Add(New OracleParameter(&quot;io_cursor2&quot;, OracleType.Cursor)).Direction = ParameterDirection.Output

Dim MyDA As New OracleDataAdapter(myCMD)

Try MyDA.Fill(Ds) Catch Myex As Exception MessageBox.Show(Myex.Message.ToString) End Try

Ds.Relations.Add(&quot;EmpDept&quot;, Ds.Tables(0).Columns(&quot;Deptno&quot;), Ds.Tables(1).Columns(&quot;Deptno&quot;)) DataGrid1.DataSource = Ds.Tables(0) Oraclecon.Close  Modify the &quot;OracleConnection&quot; string as appropriate for your environment. Press F5 to compile and run the application.</li></ol>

back to the top

<div class="references_section">