You are currently offline, waiting for your internet to reconnect

How To Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider for Oracle

This article was previously published under Q322160
SUMMARY
This step-by-step article describes how to use the .NET Managed Provider for Oracle to pass an input parameter to retrieve a Ref Cursor from an Oracle stored procedure by using the OracleDataReader object.

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, Windows 2000 Server, Windows 2000 Advanced Server, or Windows XP Professional
  • Microsoft Visual Studio .NET
  • Oracle Client 8.1.7 or later
  • Microsoft .NET Managed Provider for Oracle
NOTE: To download the .NET Managed Provider for Oracle, visit the following Microsoft Web site:This article assumes that you are familiar with the following topics:
  • Visual Basic .NET
  • ADO.NET fundamentals and syntax
back to the top

Create the Oracle Tables

This example 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,    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,35000,12,11);   INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);   INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);				
back to the top

Create the Oracle Packages

Create the following Oracle package on the Oracle server:
CREATE OR REPLACE PACKAGE curspkg_join AS         TYPE t_cursor IS REF CURSOR ;         Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);    END curspkg_join;   / 				
Create the following Oracle package body on the Oracle server:
   CREATE OR REPLACE PACKAGE BODY curspkg_join AS   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)    IS         v_cursor t_cursor;    BEGIN         IF n_EMPNO <> 0 THEN             OPEN v_cursor FOR              SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME                   FROM EMP, DEPT                   WHERE EMP.DEPTNO = DEPT.DEPTNO                   AND EMP.EMPNO = n_EMPNO;        ELSE              OPEN v_cursor FOR              SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME                   FROM EMP, DEPT                   WHERE EMP.DEPTNO = DEPT.DEPTNO;        END IF;        io_cursor := v_cursor;    END open_join_cursor1;    END curspkg_join;   / 				
back to the top

Create the Visual Basic .NET Application

  1. Follow these steps to create a Visual Basic Windows Application project:
    1. Start Microsoft Visual Studio .NET.
    2. On the File menu, point to New, and then click Project.
    3. Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.
  2. On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace.
  3. Drag a Button control from the toolbox 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 Oraclecon As New OracleConnection("Password=pwd;" & _                                                "User ID=uid;Data Source=MyOracle;")        Oraclecon.Open()        Dim myCMD As New OracleCommand()        myCMD.Connection = Oraclecon        myCMD.CommandText = "curspkg_join.open_join_cursor1"         myCMD.CommandType = CommandType.StoredProcedure        myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output        myCMD.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 123        Dim myReader As OracleDataReader        Try            myCMD.ExecuteNonQuery()        Catch myex As Exception            MsgBox(myex.Message)        End Try        myReader = myCMD.Parameters("io_cursor").Value        Dim x, count As Integer        count = 0        Do While myReader.Read()            For x = 0 To myReader.FieldCount - 1                Console.Write(myReader(x) & " ")            Next            Console.WriteLine()            count += 1        Loop        MsgBox(count & " Rows Returned.")        myReader.Close()        Oraclecon.Close()					
  6. Modify the OracleConnection string as appropriate for your environment.
  7. Press F5 to compile and to run the program.
back to the top

Additional Information

When you use the OracleDataReader object, the Ref Cursor must be retrieved from the Parameters collection of the OracleCommand object.

back to the top
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
176086 How To Retrieve Recordsets from Oracle Stored Procedures Using ADO
321718 How To Call Oracle Stored Procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider
321715 How To Call Oracle Stored Procedures That Contain Multiple Ref Cursors by Using the Oracle .NET Managed Provider
The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

back to the top
Properties

Article ID: 322160 - Last Review: 06/29/2004 21:01:10 - Revision: 3.3

Microsoft ADO.NET (included with the .NET Framework), Microsoft ADO.NET 1.1, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual Basic .NET 2003 Standard Edition

  • kbhowtomaster kboracle kbsystemdata KB322160
Feedback
>