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

Back to the top

Requirements

• 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

• Microsoft Visual Basic .NET
• ADO.NET fundamentals and syntax

Back to the top

Create the Oracle 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 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 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. Create a new Visual Basic Windows Application project. Form1 is added to the project by default.
2. Add the following code to the top of the Code window:

   Imports System.Data.OleDB
   					

3. Add the following code to the Form_Load event of Form1:

           Dim Oraclecon As New OleDbConnection("Provider=MSDAORA.1;Password=tiger;" & _
                                                "User ID=scott;Data Source=MyOracleServer;" & _
                                                "Persist Security Info=True")
           Oraclecon.Open()
   
           Dim myCMD As New OleDbCommand _
           ("{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}", Oraclecon)
           myCMD.Parameters.Add("ID", 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) & " ")
               Next
               Console.WriteLine()
               count += 1
           Loop
   
           MsgBox(count & " Rows Returned.")
   
           myReader.Close()
           Oraclecon.Close()
   					

4. Modify the OleDbConnection string as appropriate for your environment.
5. 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

Do While myReader.Read()
Loop
				

Back to the top