The information in this article applies to:
- Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
SUMMARY
This article shows how to create an ADO 1.5 project that will return a
typical Recordset from an Oracle stored procedure. This article builds on
the concepts covered in the Microsoft Knowledge Base article:
ARTICLE-ID: Q174679
TITLE : HOWTO: Retrieve Resultsets from Oracle Stored Procedures
It is almost identical to the following article in the Microsoft Knowledge
Base article that covered the topic using RDO:
ARTICLE-ID: Q174981
TITLE : HOWTO: Retrieve Typical Resultsets from Oracle Stored
Procedures
MORE INFORMATION
Knowledge Base article Q174679 gave 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. Please refer to Q174679 if
you want more information about the process.
NOTE: The Recordsets created by the Microsoft ODBC Driver for Oracle v2.0
using Oracle stored procedures are Read Only and Static. To retrieve a
Recordset requires that an Oracle Package be created.
The sample project in this article was created in Visual Basic 5.0 and uses
ADO to access and manipulate the Recordsets created by the Microsoft ODBC
Driver for Oracle v2.0. You will need to have this driver to use the
recordsets-from-stored-procedures functionality discussed in this article
and KB 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 Knowledge
Base article Q167225 that uses RDO 2.0 in its examples.
NOTE: You will need to acquire and install the MDAC 1.5 stack for the
sample in this article. Article Q175018 explains where to get MDAC 1.5 and
how to install it. MDAC 1.5 contains ADO 1.5 and the Microsoft ODBC Driver
for Oracle v2.0.
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.
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,'Janet','Reno');
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 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 ParamIn1 As ADODB.Parameter
Dim Conn As String
Dim QSQL As String
Private Sub cmdGetEveryone_Click()
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic
Rs.LockType = adLockReadOnly
Set Rs = CPw1.Execute
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
Set Rs = Nothing
End Sub
Private Sub cmdGetOne_Click()
Dim inputssn As Long
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenStatic
Rs.LockType = adLockReadOnly
inputssn = InputBox("Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Set Rs = CPw2.Execute
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.Close
Set Rs = Nothing
End Sub
Private Sub Form_Load()
'Replace <User ID>, <Password>, and <Server> with the
'appropriate parameters.
Conn = "UID=<User ID>;PWD=<Password>;driver=" _
& "{Microsoft ODBC for Oracle};SERVER=<Server>;"
Set Cn = New ADODB.Connection
Cn.ConnectionString = Conn
Cn.CursorLocation = adUseClient
Cn.Open
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
& "lname})}"
Set CPw1 = New ADODB.Command
CPw1.ActiveConnection = Cn
CPw1.CommandText = QSQL
CPw1.CommandType = adCmdText
QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, "_
& "lname})}"
Set CPw2 = New ADODB.Command
CPw2.ActiveConnection = Cn
CPw2.CommandText = QSQL
CPw2.CommandType = adCmdText
Set ParamIn1 = CPw2.CreateParameter("Input", adInteger,
adParamInput)
CPw2.Parameters.Append ParamIn1
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set ParamIn1 = Nothing
End Sub
- Go to the Project menu item and select References. Check the "Microsoft
Active Data Objects 1.5 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 Knowledge Base
article Q174679, 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.
REFERENCES
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 v20
ARTICLE-ID: Q174981
TITLE : HOWTO: Retrieve Typical Resultsets from Oracle Stored
Procedures
ARTICLE-ID: Q167225
TITLE : HOWTO: Access an Oracle Database Using ROD