ADO.NET Cookbook

2.20. Retrieving Data from an Oracle Package

Problem

Given an Oracle package that returns multiple result sets for related tables as REF CURSOR data types, you want to access this data using a DataReader and load the data into a DataSet.

Solution

Use the data type OracleType.Cursor .

The sample code creates a Command for an Oracle package CURSPKG that takes a Customer ID input parameter. The package calls a stored procedure that returns two result sets—Orders and Order Details data from Northwind for the specified customer—as Oracle REF CURSOR output parameters.

A DataAdapter is created from the Command, retrieves the Orders and Order Details result sets, and loads them into a DataSet. A relation is created between the tables and the default view for the Orders table is bound to the data grid on the form.

Next, a DataReader is created from the Command. The Orders and Order Details result sets are displayed in a text box.

The Oracle package is shown in Example 2-27, and the package body is shown in Example 2-28.

Example 2-27. File: Packages\CURSPKG

CREATE OR REPLACE PACKAGE CURSPKG
AS
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE GetCustomerOrdersWithDetails (
    pCustomerID IN CHAR,
    curOrders OUT T_CURSOR,
    curOrderDetails OUT T_CURSOR);
END CURSPKG;

Example 2-28. File: Package Bodies\CURSPKG

CREATE OR REPLACE PACKAGE BODY CURSPKG AS PROCEDURE GetCustomerOrdersWithDetails ( pCustomerID IN CHAR, curOrders OUT T_CURSOR, curOrderDetails OUT T_CURSOR ) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN ...