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

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

Back to the top