How To Retrieve Recordsets from Oracle Stored Procedures Using ADO

Article translations Article translations
Article ID: 176086 - View products that this article applies to.
This article was previously published under Q176086
For a Microsoft Visual Basic .NET version of this article, see 321718.
For a Microsoft Visual Basic .NET version of this article, see 308072.
For a Microsoft Visual Basic .NET version of this article, see 308073.
Expand all | Collapse all

On This Page

SUMMARY

This article shows how to create a Visual Basic 5.0 and ActiveX Data Objects (ADO) project or a Visual Basic 6.0 and ADO 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:
174679 How To Retrieve Typical Resultsets From Oracle Stored Procedures
It is almost identical to the following article in the Microsoft Knowledge Base article that covers the topic using Remote Data Objects (RDO):
174981 How To Retrieve Typical Resultsets From Oracle Stored Procedures
NOTE: Microsoft recommends that you use the Microsoft ODBC Driver for Oracle.

MORE INFORMATION

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 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 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 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 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
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,'Jane','Doe');
    
    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 or 6.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 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
  8. Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x 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 the following Knowledge Base article:
174679 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.

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, click the article numbers below to view the articles in the Microsoft Knowledge Base:
174679 How To Retrieve Resultsets from Oracle Stored Procedures
175018 How To Acquire and Install the Microsoft Oracle ODBC Driver
174981 How To Retrieve Typical Resultsets from Oracle Stored Procedures
167225 How To Access an Oracle Database Using RDO

Properties

Article ID: 176086 - Last Review: March 14, 2005 - Revision: 8.5
APPLIES TO
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Open Database Connectivity 2.0
  • Microsoft Open Database Connectivity 2.0
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.0
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
Keywords: 
kbdatabase kbdriver kbhowto kboracle KB176086

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com