Microsoft KB Archive/308073

= How To Use a DataReader Against an Oracle Stored Procedure in Visual Basic .NET =

Article ID: 308073

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q308073



For a Microsoft Visual C# .NET version of this article, see 309361.

For a Microsoft Visual C++ .NET version of this article, see 309362.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.OleDb

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

REFERENCES



SUMMARY
This step-by-step article uses the DataReader object to retrieve data from an Oracle stored procedure. You can use the DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row is ever in memory.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
 * Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
 * Microsoft Visual Studio .NET
 * Oracle Client 8.1.7 or later

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. The Oracle Scott/Tiger schema is included with the default 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
 Create a new Visual Basic Windows Application project. Form1 is added to the project by default.  Add the following code to the top of the Code window: Imports System.Data.OleDB   Add the following code to the Form_Load event of Form1: Dim Oraclecon As New OleDbConnection(&quot;Provider=MSDAORA.1;Password=tiger;&quot; & _                                            &quot;User ID=scott;Data Source=MyOracleServer;&quot; & _                                             &quot;Persist Security Info=True&quot;) Oraclecon.Open

Dim myCMD As New OleDbCommand _ (&quot;{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}&quot;, Oraclecon) myCMD.Parameters.Add(&quot;ID&quot;, OleDbType.Numeric, 4).Value = 123

Dim myReader As OleDbDataReader myReader = myCMD.ExecuteReader

Dim x, count As Integer

count = 0 Do While myReader.Read For x = 0 To myReader.FieldCount - 1 Console.Write(myReader(x) & &quot; &quot;) Next Console.WriteLine count += 1 Loop

MsgBox(count & &quot; Rows Returned.&quot;)

myReader.Close Oraclecon.Close  Modify the OleDbConnection string as appropriate for your environment. Press the F5 key to compile and run the application. Notice that the data from the Oracle stored procedure appears in the Debug window, and a row count appears in a message box.

back to the top

Additional Information
Notice that the code loops through the DataReader: Do While myReader.Read Loop This is because the DataReader reads only one line at a time.

back to the top

