HOWTO: Retrieve Recordsets from Oracle Stored Procs Using ADO

Last reviewed: November 3, 1997
Article ID: Q176086
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

  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,'Janet','Reno');
       
         COMMIT;
           /
    
    

  2. 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;
          /
    
    

  3. 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;
          /
    
    

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

  5. Place the following controls on the form:

       Control     Name             Text/Caption
       -----------------------------------------
       Button      cmdGetEveryone   Get Everyone
       Button      cmdGetOne        Get One
    
    

  6. 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.

  7. 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
    
    

  8. Go to the Project menu item and select References. Check the "Microsoft Active Data Objects 1.5 Library."

  9. 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


Additional query words: oracle stored procedures ado msorcl32.dll

(c) Microsoft Corporation 1997, All Rights Reserved.
Contributions by Sam Carpenter, Microsoft Corporation
Keywords : vb5all vb5howto
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.