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

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

1. Create a new Visual Basic Windows Application project. By default, Form1 is added to the project.
2. On the Project menu, click Add Reference, and then set a reference for System.Data.OracleClient.
3. Add a command button and a Datagrid object to the form.
4. Add the following code at the top of the Code window:

   Imports System.Data.OracleClient
   					

5. Add the following code to the Button1_Click event of Form1:

           Dim Ds As New DataSet()
           Dim Oraclecon As New OracleConnection("Data Source=YourOracle;User ID=scott;Password=tiger")
          
           Oraclecon.Open()
   
   
           Dim myCMD As New OracleCommand()
           myCMD.Connection = Oraclecon
           myCMD.CommandText = "multiRefCursors.EmpDept"
           myCMD.CommandType = CommandType.StoredProcedure
           myCMD.Parameters.Add(New OracleParameter("io_cursor1", OracleType.Cursor)).Direction = ParameterDirection.Output
           myCMD.Parameters.Add(New OracleParameter("io_cursor2", 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("EmpDept", Ds.Tables(0).Columns("Deptno"), Ds.Tables(1).Columns("Deptno"))
           DataGrid1.DataSource = Ds.Tables(0)
           Oraclecon.Close()
   					

6. Modify the "OracleConnection" string as appropriate for your environment.
7. Press F5 to compile and run the application.

back to the top