ID: Q176086
The information in this article applies to:
This article shows how to create a Visual Basic 5.0 and ActiveX Data Objects (ADO) 1.5 project or a Visual Basic 6.0 and ADO 2.0 project that returns a typical Recordset from an Oracle stored procedure. This article builds on the concepts covered in the following Microsoft Knowledge Base article:
ARTICLE-ID: Q174679
TITLE : HOWTO: Retrieve Resultsets from Oracle Stored Procedures
ARTICLE-ID: Q174981
TITLE : HOWTO: Retrieve Typical Resultsets From Oracle Stored
Procedures
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:
ARTICLE-ID: Q174679
TITLE : HOWTO: Retrieve Resultsets from Oracle Stored Procedures
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 2.0 or 2.5. You must have this driver to use the recordsets-from-stored-procedures functionality discussed in this article and Knowledge Base article Q174679. (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:
ARTICLE-ID: Q167225
TITLE : HOWTO: Access an Oracle Database Using RDO
ARTICLE-ID: Q175018
TITLE : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
The MDAC 2.0 stack, which includes the 2.5 driver, can be downloaded from the following Web address:
http://www.microsoft.com/data/
1. 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 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 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;
/
is created by default.
Control Name Text/Caption
-----------------------------------------
Button cmdGetEveryone Get Everyone
Button cmdGetOne Get One
Module View" option and then click OK. This will allow you to view all
of the code for this project.
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
Active Data Objects 1.5 Library."
executes this query:
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
& "lname})}"
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})}"
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.
Microsoft ODBC Driver for Oracle Help File
"Oracle PL/SQL Programming" by Steven Feuerstein
"Hitchhiker's Guide to Visual Basic & SQL Server" by William Vaughn
For additional information, please see the following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q174679
TITLE : HOWTO: Retrieve Resultsets from Oracle Stored Procedures
ARTICLE-ID: Q175018
TITLE : HOWTO: Acquire and Install the Microsoft Oracle ODBC
Driver
ARTICLE-ID: Q174981
TITLE : HOWTO: Retrieve Typical Resultsets from Oracle Stored
Procedures
ARTICLE-ID: Q167225
TITLE : HOWTO: Access an Oracle Database Using ROD
Additional query words: oracle stored procedures ado msorcl32.dll kbVB500 kbVB600 kbADO150 kbADO200 kbOracle kbDriver kbODBC kbDatabase package
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: February 2, 1999