Knowledge base
HOWTO: Retrieve Typical Results from Oracle Stored Procedures
PSS ID Number: Q174981 Article Last Modified on 09-11-2001
The Information in this Article Applies TO:
Microsoft Visual Basic Enterprise Edition for Windows 5.0, 6.0 Microsoft ODBC for Oracle version 2.0 Build 2.73.7269, Build 2.73.7283.01, Build 2.73.7283.03, Build 2.73.7356 Microsoft ODBC for Oracle version 2.5 Build 2.573.2927
Summary This article shows how to create a Remote Data Object (RDO) project that returns a typical Resultset from an Oracle stored procedure This article builds on the concepts covered in the following Microsoft Knowledge Base article.:
Q174679
: HOWTO: Retrieve Results from Oracle Stored Procedures
More Information Microsoft Knowledge Base Article, MiNFORMATION
Q174679
Gives an in-depth Example of All The Possible Ways to Return A ResultSet 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 Resultsets created by the Microsoft ODBC Driver for Oracle v2.0 using Oracle stored procedures are READ ONLY and STATIC To retrieve a Resultset requires that an Oracle Package be created The sample project in.. this article was created in Visual 5.0 and uses RDO Basic to access and manipulate the Resultsets created by the Microsoft ODBC Driver for Oracle version 2.0 or higher You will need to have this driver to use the Resultsets -. from-stored-procedures functionality discussed in This Article and KB
Q174679
. (Currently, it is the only driver on the market that can return a Resultset from a stored procedure) If you want more information about using RDO 2.0 with Oracle, please see the following article in the Microsoft Knowledge Base:. Q167225
: HOWTO: Access An Oracle Database Using RDO
THIS ARTICLE IS in Two Parts. The first part is a step-by-step procedure for creating the project. The second 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, 'Sally', 'BURNETT');
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
(OnSSN 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
(OnSSN 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 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 allows you to view all of the code for this project Paste the following code into your code window:.. Option Explicit
DIM CN As Rdoconnection
Dim en as rdoenvironment
DIM CPW1 AS RDOQUERY
DIM CPW2 AS RDOQUERY
DIM RS As RdoreSultset
Dim conn as string
DIM QSQL AS String
DIM TEMPCNT AS INTEGER
Private subdgeteveryone_click ()
SET RS = CPW1.OpenResultSet (RdopenStatic, RdConcurreadOn "
While Not Rs.eof
MsgBox "Person Data:" & RS (0) & "& RS (1) &", "& RS (2)
Rs.movenext
Wend
Rs.close
SET RS = Nothing
End Sub
Private subduDgetone_click ()
DIM INPUTSSN AS Long
Inputssn = INPUTBOX ("Enter An SSN Number:") CPW2 (0) = Inputssn
SET RS = CPW2.OpenResultSet (RdopenStatic, RdConcurreadonly)
MsgBox "Person Data:" & RS (0) & "& RS (1) &", "& RS (2)
Rs.close
SET RS = Nothing
End Sub
Private sub flow_load ()
'Change the text in <> to the appropriate logon
'information.
Conn = "uid =
& "Driver = {microsoft odbc for oracle};" _ _
& "Server =
Set en = rdoenvironments (0)
En.cursordriver = rduseodbc
Set cn = en.openConnection ("", RDDrivernoprompt, false, conn
Qsql = "{Call PackPerson.allPerson ({ResultSet 9, SSN, FNAME,"
& "lname})}"
SET CPW1 = cn.createQuery ("", QSQL)
Qsql = "{Call PackPerson.onePerson (?, {ResultSet 2, SSN, FNAME," _
& "lname})}"
SET CPW2 = cn.createQuery ("", QSQL)
End Sub
Private Sub Form_Unload (Cancel AS Integer)
En.close
End Sub
Run the project. When click the "get eve cerery" Button, IT Executes The Following 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 9 or Fewer Records. as stated in
Q174679
YOU MUST Specify THE MAXIMUM NUMBER OF ROWS You Will Be Returning. please Refer to the Microsoft Odbc Driver for Oracle Help File andQ174679
For more information on this questionue. When You Click on the "get one" button packperson.oneperson (?, {resultset 2, ssn, fname, "_ &" lname})} "The stored procedure, packperson.oneperson, uses a single input parameter as the selection criteria for the Resultset it creates. Just like packperson. Allperson, The ResultSet IS Constructed Using The Table Types Defined In PackPerson. (See
Q174679
. For more information) NOTE: You can only define input parameters for Oracle stored procedures that return a Resultset You can not define output parameters for these stored procedures These two stored procedures cover the basic uses of stored procedures that return Resultsets The first one... gives you a predefined set of records (such as everyone) and the second will gives you a set of records (or just one record) based on one or more input parameters. Once you have these Resultsets, 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 article In The Microsoft Knowledge Base:
Q174679
: HOWTO: Retrieve Results from Oracle Stored Procedures
Q167225
: HOWTO: Access An Oracle Database Using RDO
Q175018
: HOWTO: Acquire and Install the Microsoft Oracle ODBC DriverAdditional query words:. Oracle stored procedures kbrdo msorcl32.dll kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbOracle kbOdbc kbDatabase kbDriver (c) Microsoft Corporation 1997, All Rights Reserved Contributions by Sam Carpenter, Keywords: kbGrpDSVBDB Issue Type: kbhowto Technology: kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbODBCSearch kbVB500 kbVB600 kbODBCOracle2737269 kbODBCOracle273728303 kbODBCOracle25732927 kbODBCOracle273728301 kbODBCOracle2737356 kbODBCOracle200Search kbODBCOracle250Search
Send feedback to Microsoft
© 2002-2003 Microsoft Corporation. All Rights Reserved.
