Home Page

Back to Papers


Introduction and Set-up

PL/SQL Package Header

PL/SQL Package Body

Visual Basic Code I

Visual Basic Code II

Conclusion and Additional Resources

 

Please note this page has moved to http://oracledoug.com/ado.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

Returning ADO Recordsets using PL/SQL and Cursor Variables

Introduction and Set-up

This paper by Alastair Vance explains how to use a combination of PL/SQL, Cursor Variables, ADO and Dynamic SQL to return Recordsets from an Oracle Database Server to a Visual Basic application.

This example was created and tested using the following:

  • Oracle 8.1.5 Database
  • Oracle 8.1.6 Client (which contains the necessary 8.1.6 OLE DB Provider drivers)
  • Demo Tables (see create and insert scripts below)
  • Microsoft ADO 2.1 or greater
  • Microsoft Visual Basic 6 SP3
  • For this example, I will use the following table structure:

    Editors Note: This table and data form part of the standard Learning Tree Oracle Course Load, so if you already have the ORA1 schema configured, these steps are unnecessary

    CREATE TABLE site ( site_id number(2) not null, location varchar2(12)) /

    Insert the following rows into the site table:

    insert into site values (1, 'PARIS'); insert into site values (2, 'BOSTON'); insert into site values (3, 'LONDON'); insert into site values (4, 'STOCKHOLM'); insert into site values (5, 'OTTAWA'); insert into site values (6, 'WASHINGTON'); insert into site values (7, 'LA'); insert into site values (8, 'TORONTO');

    Once the table has been created and populated, we can start to develop our oracle package/procedures to query the table.

    Next

    Technical Papers Utilities and Scripts Book Reviews Links
    My Resume Fun & Games Email Home