Microsoft KB Archive/321718

= How to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider =

Article ID: 321718

Article Last Modified on 1/15/2007

-

APPLIES TO


 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft ADO.NET 1.0
 * Microsoft ADO.NET 1.1

-



This article was previously published under Q321718





For a Microsoft Visual Basic 6.0 version of this article, see 176086.



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, and to display the information in a DataGrid object.

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
 * Microsoft .NET Managed Provider for Oracle

Note To download the .NET Managed Provider for Oracle, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/netframework/aa731542.aspx

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

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 &quot;create table and insert&quot; 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 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,'28-JAN-79',35000,12,30); INSERT INTO Emp VALUES(321,'Sue','Finance',555,'12-MAY-83',42000,12,10); INSERT INTO Emp VALUES(234,'Mary','Account',555,'14-AUG-82',33000,12,20);

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

Create the Visual Basic .NET application
 Follow these steps to create a Visual Basic Windows Application project:  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.  On the Project menu, click Add Reference, and then set a reference for System.Data.OracleClient.</li> Drag a Button control and a DataGrid control from the toolbox to the form.</li>  Add the following code at the top of the Code window: Imports System.Data.OracleClient </li>  Add the following code to the Button1_Click event of Form1: Dim x As Exception Dim Ds As New DataSet Dim Oraclecon As New OracleConnection(&quot;Server=YourOracle;Uid=uid;Pwd=pwd&quot;)

Oraclecon.Open

Dim myCMD As New OracleCommand myCMD.Connection = Oraclecon myCMD.CommandText = &quot;curspkg_join.open_join_cursor1&quot; myCMD.CommandType = CommandType.StoredProcedure myCMD.Parameters.Add(New OracleParameter(&quot;n_empno&quot;, OracleType.Number)).Value = 123 myCMD.Parameters.Add(New OracleParameter(&quot;io_cursor&quot;, OracleType.Cursor)).Direction = ParameterDirection.Output

Dim MyDA As New OracleDataAdapter(myCMD) Try

MyDA.Fill(Ds) Catch x           MessageBox.Show(x.Message.ToString) End Try

DataGrid1.DataSource = Ds.Tables(0)

Oraclecon.Close </li> Modify the OracleConnection string as appropriate for your environment.</li> Press F5 to compile and to run the application.</li></ol>

Additional information
The .NET Managed Provider for Oracle does not support the Open Database Connectivity (ODBC) escape syntax as does the Microsoft OLEDB Provider for Oracle. This version of the .NET Managed Provider for Oracle supports only Output Ref Cursors (not Input).

<div class="references_section">