HOWTO: Retrieve Typical Resultsets From Oracle Stored Procedures

• Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

SUMMARY

This article shows how to create an RDO project that will return a typical Resultset from an Oracle stored procedure. This article builds on the concepts covered in the Microsoft Knowledge Base article: Q174679 - HOWTO: Retrieve Resultsets from Oracle Stored Procedures.

MORE INFORMATION

Knowledge Base article 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 Basic 5.0 and uses RDO to access and manipulate the Resultsets created by the Microsoft ODBC Driver for Oracle v2.0. 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:

   ARTICLE-ID: Q167225
   TITLE     : HOWTO: Access an Oracle Database Using RDO

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,'Sally','Burnett');
   
         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 allows you to view all of the code for this project.

7. 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 Sub cmdGetEveryone_Click()
   

            Set Rs = CPw1.OpenResultset(rdOpenStatic, rdConcurReadOnly)
   
            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
   
            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 Form_Load()
   
            [ASCII 145]Change the text in <> to the appropriate logon
            [ASCII 145]information.
            Conn = "UID=<your user ID>;PWD=<your password>;" _
                    & "DRIVER={Microsoft ODBC for Oracle};" _
                    & "SERVER=<your database alias>;"
   
            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
   
   

8. Run the project.

   QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
            & "lname})}"

When you click on the "Get One" button, you see 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 Resultset. You cannot 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:

   ARTICLE-ID: Q174679
   TITLE     : HOWTO: Retrieve Resultsets from Oracle Stored Procedures

   ARTICLE-ID: Q167225
   TITLE     : HOWTO: Access an Oracle Database Using RDO

   ARTICLE-ID: Q175018
   TITLE     : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
               v2.0