HOWTO: Retrieve Typical Resultsets From Oracle Stored Procedures

原创 2004年10月11日 14:08:00

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:

<?XML:NAMESPACE PREFIX = MSHelp NS = "http://msdn.microsoft.com/mshelp" />Q174679: HOWTO: Retrieve Resultsets from Oracle Stored Procedures


More Information

Microsoft 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 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 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,'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 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()
    
             'Change the text in <> to the appropriate logon
             '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.

When you click the "Get Everyone" 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 and Q174679 for more information on this issue.

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})}" 

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

Q174679: HOWTO: Retrieve Resultsets from Oracle Stored Procedures

Q167225: HOWTO: Access an Oracle Database Using RDO

Q175018: HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Additional 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

Oracle 11g系列:数据库

Oracle 11g系列:数据库 1、创建Oracle数据库   创建Oracle数据库的最常用工具为Database Configuration Assistant(数据库配置助手)...
  • mss359681091
  • mss359681091
  • 2016年07月31日 09:58
  • 366

Calling Oracle stored procedures from Microsoft.NET

By  John Charles Olamendy June 29, 2007 IntroductionThis article is intended to illustrate how to il...
  • maliang1225
  • maliang1225
  • 2007年08月22日 22:03
  • 626

stored procedures 存储过程介绍

什么是存储过程呢? The stored procedures is a set of sqls which have been compiled and stored in the web...
  • sbitswc
  • sbitswc
  • 2013年03月04日 22:50
  • 611

Oracle and Java Stored Procedures

Oracle and Java Stored ProceduresBy Michael KlaeneBeginning with version 8i, the Oracle database inc...
  • Liubinglin
  • Liubinglin
  • 2009年05月24日 19:34
  • 727

数据仓库Unable to get list of procedures from database meta-data: Table 'mysql.proc' doesn't exist

1、将mysql_upgrade.exe和mysqlcheck.exe两个文件复制到 APMServ5.2.6\MySQL5.1\bin目录下。   2、开始->运行->输入cmd回车,在操作提示符...
  • qq_36463975
  • qq_36463975
  • 2016年10月31日 23:49
  • 134

安装yii2过程中,遇到的问题

Your requirements could not be resolved to an installable set of packages Could not fetch xxxx, plea...
  • u013934914
  • u013934914
  • 2015年11月30日 12:02
  • 1387

Stored Procedures

  • zgqtxwd
  • zgqtxwd
  • 2008年04月24日 13:43
  • 107

Spring, Hibernate and Oracle Stored Procedures

一篇英文博文,写的是利用hibernate处理存储过程中的游标等等:Motivation: While there are a few resources available online for c...
  • MrCharles
  • MrCharles
  • 2015年06月18日 14:34
  • 487

Stored Procedure存储过程基础知识详解

存储过程是做项目的必备技术,只要你面试找工作,数据库及存储过程也是必考的,下面一起来了解存储过程的简单基本技术知识: 一、存储过程基本概念? 存储过程就是作为可执行对象存放在数据库中的一个或多个S...
  • u013519551
  • u013519551
  • 2017年05月19日 15:04
  • 630

Stored Procedures in SQLite

REFs: http://www.sqliteconcepts.org/pl_proc.html http://chriswolf.heroku.com/articles/2011/0...
  • zlxadhkust
  • zlxadhkust
  • 2011年11月28日 15:43
  • 605
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HOWTO: Retrieve Typical Resultsets From Oracle Stored Procedures
举报原因:
原因补充:

(最多只允许输入30个字)