HOW TO: Use a DataReader Against an Oracle Stored Procedure in Visual C++ .NET

This article was previously published under Q309362
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Visual C# .NET version of this article, see 309361.
For a Microsoft Visual Basic .NET version of this article, see 308073.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System::Data::OleDb
  • System::Windows::Forms

IN THIS TASK

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 C++ .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)   );   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)   );				
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 C++ .NET Application

  1. Use Visual Studio .NET to create a new Managed C++ Application.
  2. Double-click the source (.cpp) file located in the Solution Explorer.
  3. Remove the default code from the source file and then paste the following code in the file:
    #include "stdafx.h"#using <mscorlib.dll>#include <tchar.h>#using <system.dll>using namespace System;#using <system.data.dll>using namespace System::Data;using namespace System::Data::OleDb;#using <system.windows.forms.dll>using namespace System::Windows::Forms;int _tmain(void){	try{		OleDbConnection *myCon = new OleDbConnection("Provider=MSDAORA.1;Data Source=myOraServer;User ID=Scott;Password=Tiger;");		myCon->Open();				OleDbCommand *myCmd = new OleDbCommand("{call curspkg_join.open_join_cursor1(?,{resultset 0,io_cursor})}", myCon);		myCmd->Parameters->Add("i",OleDbType::Integer,4);		int iVal= 0;		__box int *boxI=__box(iVal);		myCmd->Parameters->get_Item(0)->Value=boxI;				OleDbDataReader *myReader;			myReader=myCmd->ExecuteReader();			int rowCnt=0;			while(myReader->Read())		{			for(int col=0;col<myReader->FieldCount;col++)			{				Console::Write("{0}: {1}",(myReader->GetName(col))->ToString(),(myReader->GetValue(col))->ToString());				Console::WriteLine();			} 			Console::WriteLine();			rowCnt++;		}				Console::WriteLine("Number of records returned: {0}", rowCnt.ToString());                  Console::WriteLine("Hit Enter to Exit");		Console::ReadLine();		myReader->Close();		myCon->Close();	}	catch(OleDbException *mySqlEx)	{		for(int i=0;i<mySqlEx->Errors->Count;i++)		{			Console::WriteLine("Source={0};Message={1};",mySqlEx->Errors->Item[i]->Source,mySqlEx->Errors->Item[i]->Message);		}	}	catch(System::Exception *ex)	{		Console::WriteLine(ex->get_Message());	}    return 0;}					

  4. Change the connection string to point to your Oracle database. If you have created the tables and package in a schema different from Scott/Tiger, change the user name and password appropriately.
  5. Press CTRL+F5 to compile and run the project. The data from the Oracle stored procedure and the row count are displayed in the console window.
back to the top

Additional Information

Note that directly assigning a value of 0 (myCmd->Parameters->get_Item(0)->Value=0;) will not result in a compilation error. However, because managed extensions have no null literal, 0 in this assignment is treated a NULL value and will result in a run-time error.

back to the top
REFERENCES
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
176086 HOWTO: Retrieve Recordsets from Oracle Stored Procedures Using ADO
back to the top
Oracle ADO.NET .NET package stored procedure resultset parameters
Properties

Article ID: 309362 - Last Review: 01/16/2015 23:55:09 - Revision: 4.1

  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual C++ .NET 2003 Standard Edition
  • Microsoft Visual C++ .NET 2002 Standard Edition
  • kbnosurvey kbarchive kbhowtomaster kboracle kbsystemdata KB309362
Feedback