Microsoft KB Archive/320632

= HOW TO: Fill a DataSet from an Oracle Stored Procedure by Using the OLE DB .NET Data Provider with J# .NET =

Article ID: 320632

Article Last Modified on 8/7/2003

-

APPLIES TO


 * Microsoft ADO.NET 1.0
 * Microsoft Visual J# .NET 2003 Standard Edition
 * Microsoft Visual J# .NET 2003 Standard Edition
 * Microsoft ADO.NET 1.1

-



This article was previously published under Q320632



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

For a Microsoft Visual Basic .NET version of this article, see 308072.

IN THIS TASK
SUMMARY
 * Requirements
 * To Create the Package in the Oracle Database
 * To Create the Visual J# .NET Program

REFERENCES



SUMMARY
This article describes how to fill a DataSet object with the result set from an Oracle stored procedure. The DataSet object is central to supporting disconnected, distributed-data scenarios with ADO.NET. The DataSet object is a memory-resident representation of data that provides a consistent, relational programming model regardless of the data source. The DataSet object represents a complete set of data, including related tables, constraints, and relationships among the tables.

back to the top

Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
 * Microsoft Windows XP, Microsoft Windows 2000, or Microsoft Windows NT 4.0 Service Pack 6a
 * Microsoft Data Access Components (MDAC) 2.6 or later
 * Oracle 8.0 Server or later
 * Visual J# .NET
 * Oracle client installed on the client computer

This article assumes that you are familiar with the following topics:
 * Data definition language (DDL) queries in Oracle
 * The Visual J# .NET syntax
 * ActiveX Data Objects (ADO)

back to the top

To Create the Package in the Oracle Database
  Use the following code to create a table that is named Person: CREATE TABLE person (ssn    NUMBER(9) PRIMARY KEY,           fname   VARCHAR2(15),           lname   VARCHAR2(20));

INSERT INTO person VALUES(000000001,'Chris','Gray');

INSERT INTO person VALUES(000000002,'Ken','Kwok');

INSERT INTO person VALUES(000000003,'Stephanie','Bourne');

COMMIT;   Use the following code to create the package header: CREATE OR REPLACE PACKAGE packperson AS            TYPE tssn is TABLE of  NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname is TABLE of VARCHAR2(15) INDEX BY BINARY_INTEGER; TYPE tlname is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;

PROCEDURE allperson (ssn   OUT     tssn,                      fname  OUT     tfname,                      lname  OUT     tlname); END packperson; /                      Use the following code to create the package body: CREATE OR REPLACE PACKAGE BODY packperson AS

PROCEDURE allperson (ssn   OUT     tssn,                      fname  OUT     tfname,                      lname  OUT     tlname) IS            CURSOR person_cur IS                     SELECT ssn, fname, lname FROM person;

percount NUMBER DEFAULT 1;

BEGIN FOR singleperson IN person_cur LOOP ssn(percount) := singleperson.ssn; fname(percount) := singleperson.fname; lname(percount) := singleperson.lname; percount := percount + 1; END LOOP; END;

END; /                    

back to the top

To Create the Visual J# .NET Program
 Create a new Visual J# .NET Windows program. By default, Form1 is added to the program. Drag a DataGrid control and a Button control to Form1. By default, DataGrid1 and Button1 are added to Form1.  Add the following code as the first line in the Code window: import System.Data.OleDb.*; </li>  Add the following code in the Button1_Click event: try {         OleDbConnection cnOra = new OleDbConnection (&quot;Provider=Provider;Data Source=Server;&quot;               + &quot;user id=User;password=Password;&quot;               + &quot;persist security info=false;&quot;);

OleDbCommand cmdPerson = new OleDbCommand (&quot;{call PackPerson.allPerson&quot;                       + &quot;({resultset 3, ssn, fname, lname})}&quot;, cnOra);

OleDbDataAdapter daPerson = new OleDbDataAdapter(cmdPerson);

cnOra.Open;

DataSet ds = new DataSet;

daPerson.Fill(ds, &quot;Person&quot;);

dataGrid1.set_DataSource (ds.get_Tables.get_Item(&quot;Person&quot;)); }    catch (Exception error) {         MessageBox.Show (&quot;Error = &quot; + error); }       finally {               cnOra.Close; }                   </li>  Modify the OleDbConnection string as appropriate for your environment: OleDbConnection cnOra

= new OleDbConnection

(&quot;Provider=Provider;Data Source=Server;&quot;

+ &quot;user id=User;password=Password;&quot;

+ &quot;persist security info=false;&quot;); </li> Press F5 to build and then run the program.</li> Click Button1. The results appear in the DataGrid control.</li></ol>

NOTE: You must use an Oracle version 8.x server to run the program. Only Oracle 8.0 and later support retrieving result sets and running stored procedures.

back to the top

<div class="references_section">