HOWTO: Retrieve Resultsets from Oracle Stored Procedures

• Microsoft Visual Basic Enterprise Edition for Windows, version 5.0

SUMMARY

This article demonstrates how to use Visual Basic 5.0 Enterprise Edition, RDO, the Microsoft ODBC Driver for Oracle v2.0, and an Oracle PL/SQL package to retrieve resultsets from an Oracle stored procedure.

MORE INFORMATION

With the release of the Microsoft ODBC Driver for Oracle v2.0, you can now retrieve resultsets from Oracle stored procedures. By creating Oracle stored procedures that return parameters of type TABLE, you can return row and column data that can then be manipulated and displayed as a resultset. This article uses the example in the Help file for the Microsoft ODBC Driver for Oracle v2.0 and shows how to use that example in Visual Basic.

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.

Before you can start working with the Visual Basic application, you must create an Oracle package called SimplePackage. SimplePackage is taken from the Help File for Microsoft ODBC Driver for Oracle: Advanced Topics: "Returning Array Parameters from Stored Procedures".

The following package definition and package body for SimplePackage need to be added to your Oracle server either through SQL*Plus or through the Data Tools in Visual Basic 5.0 Enterprise edition.

Step 1

    CREATE OR REPLACE PACKAGE SimplePackage AS

      TYPE t_id is TABLE of  NUMBER(5)
       INDEX BY BINARY_INTEGER;

      TYPE t_Course is TABLE of VARCHAR2(10)
       INDEX BY BINARY_INTEGER;

      TYPE t_Dept is TABLE of VARCHAR2(5)
       INDEX BY BINARY_INTEGER;

      TYPE t_pk1Type1 IS TABLE OF VARCHAR2(100)
       INDEX BY BINARY_INTEGER;

      TYPE t_pk1Type2 IS TABLE OF NUMBER(5)
       INDEX BY BINARY_INTEGER;

      PROCEDURE proc1
      (   o_id           OUT  t_id,
            ao_course      OUT  t_Course,
            ao_dept        OUT  t_Dept
      );

      PROCEDURE proc2
        (
            i_Arg1         IN   NUMBER,
            ao_Arg2        OUT  t_pk1Type1,
            ao_Arg3        OUT  t_pk1Type2
      );

    END SimplePackage;

Step 2

    CREATE OR REPLACE PACKAGE BODY SimplePackage AS

      PROCEDURE  proc1
        (
            o_id           OUT  t_id,
            ao_course      OUT  t_Course,
            ao_dept        OUT  t_Dept
        )
      AS
      BEGIN

            o_id(1):= 200;
            ao_course(1) :=  'M101';
            ao_dept(1) :=  'EEE' ;

            o_id(2) := 201;
            ao_course(2) :=  'PHY320';
            ao_dept(2) :=  'ECE' ;

      END proc1;

      PROCEDURE proc2
        (
            i_Arg1         IN   NUMBER,
            ao_Arg2        OUT  t_pk1Type1,
            ao_Arg3        OUT  t_pk1Type2
        )
      AS
            i  NUMBER;
    BEGIN

            FOR i IN 1 .. i_Arg1 LOOP
              ao_Arg2(i) := 'Row Number ' || to_char(i);
            END LOOP;

            FOR i IN 1 .. i_Arg1 LOOP
              ao_Arg3(i) := i;
            END LOOP;

      END proc2;

    END SimplePackage;

Step-by-Step Example

This example project uses a simple form to call the two procedures in the SimplePackage package. Both procedures (Proc1 and Proc2) return arrays (Oracle TABLE data type) that are converted to resultsets by the Microsoft ODBC Driver for Oracle v2.0. These resultsets are manipulated and displayed in the project using the Remote Data Object. Here are the steps to create the project:

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

2. Place the following controls on the form:

      Control     Name      Text/Caption
      ----------------------------------
      Button      cmdProc1A  Proc1A
      Button      cmdProc1B  Proc1B
      Button      cmdProc2A  Proc2A
      Button      cmdProc2B  Proc2B
      Text Box    txtZero1
      Text Box    txtZero2
      Text Box    txtOne1
      Text Box    txtOne2
   
   

   txtZero1           txtOne1
   txtZero2           txtOne2

      Option Explicit
      Dim Cn As rdoConnection
      Dim En As rdoEnvironment
      Dim CPw1 As rdoQuery
      Dim CPw2 As rdoQuery
      Dim CPw3 As rdoQuery
      Dim CPw4 As rdoQuery
      Dim Rs As rdoResultset
      Dim Conn As String
      Dim QSQL As String

      Private Sub cmdProc1A_Click()

        Set Rs = CPw1.OpenResultset(rdOpenStatic, rdConcurReadOnly)

        txtZero1 = Rs(0)
        txtOne1 = Rs(1) & "       " & Rs(2)

        Rs.MoveNext

        txtZero2 = Rs(0)
        txtOne2 = Rs(1) & "   " & Rs(2)

        Rs.Close

        MsgBox "Done"

      End Sub

      Private Sub cmdProc1B_Click()

        Dim tempOne1 As String
        Dim tempOne2 As String

        Set Rs = CPw2.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

        txtZero1 = Rs(0)

        Rs.MoveNext

        txtZero2 = Rs(0)

        Rs.MoreResults

        tempOne1 = Rs(0)

        Rs.MoveNext

        tempOne2 = Rs(0)

        Rs.MoreResults

        txtOne1 = tempOne1 & "       " & Rs(0)

        Rs.MoveNext

        txtOne2 = tempOne2 & "   " & Rs(0)

         Rs.Close

        MsgBox "Done"

      End Sub

      Private Sub cmdProc2A_Click()

        CPw3(0) = 2
        Set Rs = CPw3.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

        txtZero1 = Rs(0)
        txtOne1 = Rs(1)

        Rs.MoveNext

        txtZero2 = Rs(0)
        txtOne2 = Rs(1)

        Rs.Close

        MsgBox "Done"

       End Sub

      Private Sub cmdProc2B_Click()

        CPw4(0) = 2
        Set Rs = CPw4.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

        txtZero1 = Rs(0)

        Rs.MoveNext

        txtZero2 = Rs(0)

        Rs.MoreResults

        txtOne1 = Rs(0)

        Rs.MoveNext

        txtOne2 = Rs(0)

        Rs.Close

        MsgBox "Done"

      End Sub

      Private Sub Form_Load()

        Conn = "UID=<user ID>;PWD=<password>;"_
            & "driver={Microsoft ODBC for Oracle};SERVER=RonOracle;"

        Set En = rdoEnvironments(0)
        En.CursorDriver = rdUseOdbc
        Set Cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)

        QSQL = "{call SimplePackage.Proc1({resultset 3, o_id , " _
               & "ao_course, ao_dept})}"
        Set CPw1 = Cn.CreateQuery("", QSQL)

        QSQL = "{call SimplePackage.Proc1({resultset 3, o_id}, " _
               & "{resultset 3, ao_course}, {resultset 3, ao_dept})}"
        Set CPw2 = Cn.CreateQuery("", QSQL)

        QSQL = "{call SimplePackage.Proc2(?,{resultset 3, ao_Arg2," _
               & " ao_Arg3})}"
        Set CPw3 = Cn.CreateQuery("", QSQL)

        QSQL = "{call SimplePackage.Proc2(?,{resultset 3, ao_Arg2}, " _
               & "{resultset 3, ao_Arg3})}"
        Set CPw4 = Cn.CreateQuery("", QSQL)

      End Sub

      Private Sub Form_Unload(Cancel As Integer)

        En.Close

      End Sub