Article ID: 248754
Article Last Modified on 7/1/2004
APPLIES TO
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 1
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
This article was previously published under Q248754
SUMMARY
Hierarchical recordsets in ActiveX Data Objects (ADO) give an alternative to using JOIN and GROUP BY syntax when you need to access parent-child and summary data. It is possible to create these recordsets based on recordsets created from Oracle procedures. This article gives an example of the ADO SHAPE command syntax and ADO code necessary for producing hierarchical recordsets from an Oracle Stored Procedure.
MORE INFORMATION
Sample Code
Run the following DDL script on your Oracle server:
DROP TABLE Cust; CREATE TABLE Cust (CustID NUMBER(22,6) PRIMARY KEY, Name VARCHAR2(50)); INSERT INTO Cust VALUES(100,'Sam'); INSERT INTO Cust VALUES(222,'Kent'); INSERT INTO Cust VALUES(333,'Sally'); DROP TABLE Ord; CREATE TABLE Ord (ORDID Number(22,4) Primary Key, CustID Number(22,6), Total Number(8,2)); INSERT INTO Ord VALUES(133,100,231); INSERT INTO Ord VALUES(123,222,899); INSERT INTO Ord VALUES(122,333,988); COMMIT; /
Create the following package on your Oracle server:
CREATE OR REPLACE PACKAGE packOrders AS TYPE tTotal is TABLE of NUMBER(8,2) INDEX BY BINARY_INTEGER; TYPE CCustid is TABLE of NUMBER(22,6) INDEX BY BINARY_INTEGER; TYPE OCustid is TABLE of NUMBER(22,6) INDEX BY BINARY_INTEGER; TYPE tname is TABLE of VARCHAR2(50) INDEX BY BINARY_INTEGER; PROCEDURE GetCust (name OUT tname, Custid OUT CCUstid); PROCEDURE GetOrders (Total OUT tTotal, Custid OUT OCUstid); END packOrders; /
Create the following package body on your Oracle server:
CREATE OR REPLACE PACKAGE BODY packOrders AS PROCEDURE GetCust (name OUT tname, Custid OUT CCustid) IS CURSOR Orders_cur IS SELECT Custid, Name FROM Cust; percount NUMBER DEFAULT 1; BEGIN FOR singleperson IN Orders_cur LOOP Custid(percount) := Singleperson.Custid; name(percount) := singleperson.name; percount := percount + 1; END LOOP; END; PROCEDURE GetOrders (Total OUT tTotal, Custid OUT OCustid) IS CURSOR person_cur IS SELECT Custid, total FROM Ord; percount NUMBER DEFAULT 1; BEGIN FOR singleperson IN person_cur LOOP Custid(percount) := Singleperson.Custid; Total(percount) := singleperson.total; percount := percount + 1; END LOOP; END; END; /
- Open a new project in Visual Basic Enterprise edition. Form1 is created by default.
- From the Project menu, click References, and select Microsoft ActiveX Data Objects.
- From the Project menu, click Components, and select the Microsoft DataGrid Control 6.0.
Place the following controls on the form:
Control Name -------------------- Button Command1 DataGrid DataGrid1 DataGrid DataGrid2
Place the following code in the code module of Form1:
Private Sub Command1_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim lcCOnn As String lcCOnn = "Provider=MSDATASHAPE;Data Provider=Msdaora;" & _ "Data Source=dseoracle8;User Id=demo;Password=demo" cn.Open lcCOnn rs.Open "SHAPE {{call packOrders.GetOrders({resultset 22, CustID, Total})}} " & _ "AS Command1 APPEND ({{call packOrders.GetCust({resultset 9, CustId, Name}) }} " & _ "AS Command2 RELATE 'Custid' TO 'Custid') AS Command2", cn Set DataGrid1.DataSource = rs Set DataGrid2.DataSource = rs(2).Value End Sub Private Sub Form_Load() Command1.Caption = "Populate Grid" End Sub
- Run the Project and click the Command1 button.RESULT: The Grid populates with the parent and child recordsets.
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
174981 How To Retrieve Typical Resultsets From Oracle Stored Procedures
189657 How To Use the ADO SHAPE Command
185425 INFO ADO Hierarchical Recordsets via SHAPE APPEND w/C++/VBA/Java
Keywords: kbhowto kboracle kbdatabase KB248754