How To Create Hierarchical Recordsets from an Oracle Stored Procedure (248754)

MORE INFORMATION

Sample Code

1. 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;
   / 
   
   					

2. 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;
   / 
   
   
   					

3. 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;
   / 
   
   					

4. Open a new project in Visual Basic Enterprise edition. Form1 is created by default.
5. From the Project menu, click References, and select Microsoft ActiveX Data Objects.
6. From the Project menu, click Components, and select the Microsoft DataGrid Control 6.0.
7. Place the following controls on the form:

      Control     Name
      --------------------
      Button      Command1
      DataGrid    DataGrid1
      DataGrid    DataGrid2
   
   					

8. 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
   					

9. Run the Project and click the Command1 button.RESULT: The Grid populates with the parent and child recordsets.