The following Knowledge Base article gives an in-depth example, using RDO, of all the possible ways to return a Recordset back from a stored procedure. The example in this article is a simplified version:

174679  (http://support.microsoft.com/kb/174679/EN-US/ ) How To Retrieve Resultsets from Oracle Stored Procedures

NOTE: The Recordsets created by the Microsoft ODBC Driver for Oracle , using Oracle stored procedures, are Read Only and Static. Retrieving a Recordset requires you to create an Oracle Package.

You can create the sample project in this article in Visual Basic 5.0 or 6.0 and use ADO to access and manipulate the Recordsets created by the Microsoft ODBC Driver for Oracle version. You must have this driver to use the recordsets-from-stored-procedures functionality discussed in this Knowledge Base article:

174679  (http://support.microsoft.com/kb/174679/EN-US/ ) How To Retrieve Resultsets from Oracle Stored Procedures

(Currently, it is the only driver on the market that can return a Recordset from a stored procedure.) If you want additional information about using Visual Basic with Oracle, please see the following Knowledge Base article, which uses RDO 2.0 in its examples:

167225  (http://support.microsoft.com/kb/167225/EN-US/ ) How To Access an Oracle Database Using RDO

NOTE: You must acquire and install the MDAC 2.1 or later stack for the sample in this article. The following Microsoft Knowledge Base article explains how to get the Oracle and MDAC components:

175018  (http://support.microsoft.com/kb/175018/EN-US/ ) How To Acquire and Install the Microsoft Oracle ODBC Driver

MDAC 1.5 contains ADO 1.5 and the Microsoft ODBC Driver for Oracle version 2.0.

The MDAC 2.x stack, which includes the 2.573 driver, can be downloaded from the following Web address:

http://msdn.microsoft.com/en-us/data/aa937729.aspx (http://msdn.microsoft.com/en-us/data/aa937729.aspx)

This article is broken up into two parts. The first part is a step-by-step procedure for creating the project. The second part is a detailed discussion about the interesting parts of the project.

Back to the top

Step-by-Step Example

Run the following DDL script on your Oracle server:

DROP TABLE person;

CREATE TABLE person

(ssn NUMBER(9) PRIMARY KEY,

fname VARCHAR2(15),

lname VARCHAR2(20));

INSERT INTO person VALUES(555662222,'Sam','Goodwin');

INSERT INTO person VALUES(555882222,'Kent','Clark');

INSERT INTO person VALUES(666223333,'Jane','Doe');

COMMIT;

/

Create the following package on your Oracle server:

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

PROCEDURE oneperson

(onessn IN NUMBER,

ssn OUT tssn,

fname OUT tfname,

lname OUT tlname);

END packperson;

/

Create the following package body on your Oracle server:

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;

PROCEDURE oneperson

(onessn IN NUMBER,

ssn OUT tssn,

fname OUT tfname,

lname OUT tlname)

IS

CURSOR person_cur IS

SELECT ssn, fname, lname

FROM person

WHERE ssn = onessn;

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;

/

Open a new project in Visual Basic 5.0 or 6.0 Enterprise edition. Form1 is created by default.

Place the following controls on the form:

Control Name Text/Caption

-----------------------------------------

Button cmdGetEveryone Get Everyone

Button cmdGetOne Get One

From the Tools menu, select the Options item. Click the "Default Full Module View" option and then click OK. This will allow you to view all of the code for this project.

Paste the following code into your code window:

Option Explicit

Dim Cn As ADODB.Connection

Dim CPw1 As ADODB.Command

Dim CPw2 As ADODB.Command

Dim Rs As ADODB.Recordset

Dim Conn As String

Dim QSQL As String

Dim inputssn As Long

Private Sub cmdGetEveryone_Click()

Set Rs.Source = CPw1

Rs.Open

While Not Rs.EOF

MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)

Rs.MoveNext

Wend

Rs.Close

End Sub

Private Sub cmdGetOne_Click()

Set Rs.Source = CPw2

inputssn = InputBox("Enter the SSN you wish to retrieve:")

CPw2(0) = inputssn

Rs.Open

MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)

Rs.Close

End Sub

Private Sub Form_Load()

'Replace <User ID>, <Password>, and <Server> with the

'appropriate parameters.

Conn = "UID=*****;PWD=*****;driver=" _

& "{Microsoft ODBC for Oracle};SERVER=dseOracle;"

Set Cn = New ADODB.Connection

With Cn

.ConnectionString = Conn

.CursorLocation = adUseClient

.Open

End With

QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _

& "lname})}"

Set CPw1 = New ADODB.Command

With CPw1

Set .ActiveConnection = Cn

.CommandText = QSQL

.CommandType = adCmdText

End With

QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _

& "lname})}"

Set CPw2 = New ADODB.Command

With CPw2

Set .ActiveConnection = Cn

.CommandText = QSQL

.CommandType = adCmdText

.Parameters.Append .CreateParameter(, adInteger, adParamInput)

End With

Set Rs = New ADODB.Recordset

With Rs

.CursorType = adOpenStatic

.LockType = adLockReadOnly

End With

End Sub

Private Sub Form_Unload(Cancel As Integer)

Cn.Close

Set Cn = Nothing

Set CPw1 = Nothing

Set CPw2 = Nothing

Set Rs = Nothing

End Sub

Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x Library."

Run the project. When you click on the "Get Everyone" button, it executes this query:

QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_

& "lname})}"

This query is executing the stored procedure "allperson," which is in the package "packperson" (referenced as "packperson.allperson"). There are no input parameters and the procedure is returning three arrays (ssn, fname, and lname) each with nine or fewer records. As stated in the following Knowledge Base article:

174679  (http://support.microsoft.com/kb/174679/EN-US/ ) How To Retrieve Resultsets from Oracle Stored Procedures

you must specify the maximum number of rows you will be returning. Please refer to the Microsoft ODBC Driver for Oracle Help File and Knowledge Base article Q174679 for more information on this issue.

When you click on the "Get One," button it brings up an input box that prompts you for an SSN. Once you input a valid SSN and click OK, this query is executed:

QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, "_

& "lname})}"

The stored procedure, packperson.oneperson, uses a single input parameter as the selection criteria for the Recordset it creates. Just like packperson.allperson, the Recordset is constructed using the table types defined in packperson. (See Knowledge Base article Q174679 for more information.)

NOTE: You can only define input parameters for Oracle stored procedures that return a Recordset. You cannot define output parameters for these stored procedures.

These two stored procedures cover the basic uses of stored procedures that return Recordsets. The first one will give you a predefined set of records (i.e. everyone) and the second one will give you a set of records (or just one record) based on one or more input parameters. Once you have these recordsets, you can do inserts, updates, and deletes either through stored procedures or SQL that you create on the client.