Resources
Data Access has been discontinued. Please refer to this page for more information.

How to: Execute Oracle Stored Procedures Returning RefCursors

In this topic you will learn how to execute Oracle stored procedures that return SYS_REFCURSOR as out parameters. With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

Suppose, you have two tables named Products and Categories with one-to-many relation.

And you have two stored procedures named SPSingleRefCur and SPTwoRefCursor. The first one is a simple stored procedure having an SYS_REFCURSOR out parameter. The procedure returns all category rows via this cursor.

CREATE OR replace PROCEDURE SPSingleRefCur(catCur OUT SYS_REFCURSOR )   
IS   
BEGIN   
OPEN catCur FOR SELECT * FROM "CATEGORIES";   
END;

The second procedure have two SYS_REFCURSOR out parameters. The procedure returns all rows from the Categories and Products tables. It will be used to demonstrate how to obtain multiple result sets using the Telerik Data Access ADO API.

CREATE OR replace PROCEDURE SPTwoRefCursor (catCur OUT SYS_REFCURSOR, prodCur OUT SYS_REFCURSOR )    
IS    
BEGIN    
OPEN catCur FOR SELECT  * FROM "CATEGORIES";        
OPEN prodCur FOR SELECT * FROM "PRODUCTS";   
END;

Telerik Data Access uses the ADO.NET (Oracle.DataAcces.Client) Provider from Oracle called ODP.NET and the ODP.NET Managed driver (Oracle.ManagedDataAcces.Client). In order to create a new model based on a Oracle database, you have to install either the ODP.NET Driver from Oracle, or ODP.NET Oracle, Managed Driver.

Suppose, you have created a model that contains two persistent classes - Product and Category:

The Product class

public partial class Product
{
    public virtual int ProductID { get; set; }
    public virtual string ProductName { get; set; }
    public virtual int? SupplierID { get; set; }
    public virtual int? CategoryID { get; set; }
    public virtual string QuantityPerUnit { get; set; }
    public virtual decimal? UnitPrice { get; set; }
    public virtual short? UnitsInStock { get; set; }
    public virtual short? UnitsOnOrder { get; set; }
    public virtual short? ReorderLevel { get; set; }
    public virtual bool Discontinued { get; set; }
    public virtual Category Category { get; set; }
}

Partial Public Class Product
    Public Overridable Property ProductID() As Integer
    Public Overridable Property ProductName() As String
    Public Overridable Property SupplierID() As Integer?
    Public Overridable Property CategoryID() As Integer?
    Public Overridable Property QuantityPerUnit() As String
    Public Overridable Property UnitPrice() As Decimal?
    Public Overridable Property UnitsInStock() As Short?
    Public Overridable Property UnitsOnOrder() As Short?
    Public Overridable Property ReorderLevel() As Short?
    Public Overridable Property Discontinued() As Boolean
    Public Overridable Property Category() As Category
End Class

The Category class

public partial class Category
{
    public virtual int CategoryID { get; set; }
    public virtual string CategoryName { get; set; }
    public virtual string Description { get; set; }
    public virtual byte[] Picture { get; set; }

    private IList<Product> _products = new List<Product>();
    public virtual IList<Product> Products
    {
        get
        {
            return this._products;
        }
    }

}

Partial Public Class Category
    Public Overridable Property CategoryID() As Integer
    Public Overridable Property CategoryName() As String
    Public Overridable Property Description() As String
    Public Overridable Property Picture() As Byte()

    Private _products As IList(Of Product) = New List(Of Product)()
    Public Overridable ReadOnly Property Products() As IList(Of Product)
        Get
            Return Me._products
        End Get
    End Property
End Class

You need to add a reference to either the Oracle.DataAccess.dll assembly or the Oracle.ManagedDataAccess.dll assembly.

Executing Stored Procedures

There are two ways to execute stored procedures and get the out parameters:

  • Use the generic OpenAccessContext.ExecuteQuery<T> method - Read more.
  • Create a new OACommand and execute the stored procedure using the OACommand.ExecuteReader method. Read more.

The first approach is easier and involves less code than the second approach. The second approach gives you more control of the result because you can get a DbDataReader from the OACommand.

The console application used in this demo is configured as described in the Consuming a Model - Configuration article.

Using the OpenAccessContext.ExecuteQuery<T> Method

The first stored procedure (SPSingleRefCur) will be executed by using the generic OpenAccessContext.Execute<T> method. Because you are using an Oracle specific type in the stored procedures, you need to pass an OracleParameter to the context. You need to set the OracleDbType property of the OracleParameter to OracleDbType.RefCursor. Next, when the parameter is set up, you need to call the ExecuteQuery<T> method of the context to get materialized list of Category objects. The following code-snippet demonstrates how to achieve this:

using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;

namespace OracleSP
{
   class Program
   {
       static void Main(string[] args)
       {
           using (FluentModel ctx = new FluentModel())
           {
               OracleParameter cursorParameter = new OracleParameter();
               cursorParameter.ParameterName = "catCur";
               cursorParameter.Direction = ParameterDirection.Output;
               cursorParameter.OracleDbType = OracleDbType.RefCursor;

               IList<Category> categories = ctx.ExecuteQuery<Category>("SPSingleRefCur", 
                    CommandType.StoredProcedure, cursorParameter);
           }
       }
   }
}

Module Module1
    Sub Main()
        Using ctx As New FluentModel()
            Dim cursorParameter As New Oracle.DataAccess.Client.OracleParameter()

            cursorParameter.ParameterName = "catCur"
            cursorParameter.Direction = ParameterDirection.Output
            cursorParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor

            Dim categories As IList(Of Category) = ctx.ExecuteQuery(Of Category)("SPSingleRefCur", 
                CommandType.StoredProcedure, cursorParameter)
        End Using
    End Sub
End Module

Although the direction of the parameter is set to ParameterDirection.Output the result set is returned via the reader, not in the Value property of the parameter. This reader is then materialized a list of Category objects. Note that if the specified type is a persistent type (as in this example), then the returned instances are automatically managed by the context.

Using the OACommand.ExecuteReader Method

If you have a stored procedure that returns two out parameters of type SYS_REFCURSOR (e.g. the SPTwoRefCursor procedure), then using the generic ExecuteQuery<T> method is no longer appropriate. If you are using the ExecuteQuery<T> method, you can not get both result sets materialized. Only the first result set, which is obtained by the underlying reader will be materialized. In this case, you need to use the second approach, i.e. execute the stored procedure by using an OACommand.

The second way to execute stored procedures is to use the OACommand class. With this approach, you have the ability to work with the new lower level ADO API. The Telerik Data Access ADO API introduces the Translate<T> method, which can be used to materialize a DbDataReader object to persistent capable or non-persistent capable objects.

The example here is similar to the first one. What you need is just another parameter, because you need two output parameters for the SPTwoRefCursor procedure. Once the parameters are initialized, you pass them to the OACommand. Next you execute the command by calling the ExecuteReader method to get a data reader. With the reader in hand, you use the generic Translate<T> method on the OpenAccessContext to materialize instances of the Category entity from the reader. By executing the reader.NextResult() method, you switch to the next result set and get the values of the second out parameter. These values could also be materialized with the Translate<T> method but this time you will get objects of type Product.

using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
using Telerik.OpenAccess.Data.Common;

namespace OracleSP
{
   class Program
   {
       static void Main(string[] args)
       {
           using (FluentModel ctx = new FluentModel())
           {
               using (OACommand cmd = ctx.Connection.CreateCommand())
               {
                   OracleParameter refCurPar1 = new OracleParameter();
                   refCurPar1.ParameterName = "catCur";
                   refCurPar1.Direction = ParameterDirection.Output;
                   refCurPar1.OracleDbType = OracleDbType.RefCursor;

                   OracleParameter refCurPar2 = new OracleParameter();
                   refCurPar2.ParameterName = "prodCur";
                   refCurPar2.Direction = ParameterDirection.Output;
                   refCurPar2.OracleDbType = OracleDbType.RefCursor;

                   cmd.CommandText = "SPTwoRefCursor";
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddRange(new object[] { refCurPar1, refCurPar2 });

                   using (OADataReader reader = cmd.ExecuteReader())
                   {
                       IEnumerable<Category> categories = ctx.Translate<Category>(reader);
                       reader.NextResult();
                       IEnumerable<Product> products = ctx.Translate<Product>(reader);
                   }
               }
           }
       }
   }
}

Module Module1
    Sub Main()
        Using ctx As New FluentModel()
            Using cmd As Telerik.OpenAccess.Data.Common.OACommand = ctx.Connection.CreateCommand()

                Dim refCurPar1 As New Oracle.DataAccess.Client.OracleParameter()
                refCurPar1.ParameterName = "catCur"
                refCurPar1.Direction = ParameterDirection.Output
                refCurPar1.OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor

                Dim refCurPar2 As New Oracle.DataAccess.Client.OracleParameter()
                refCurPar2.ParameterName = "prodCur"
                refCurPar2.Direction = ParameterDirection.Output
                refCurPar2.OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor

                cmd.CommandText = "SPTwoRefCursor"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(New Object() {refCurPar1, refCurPar2})

                Using reader As Telerik.OpenAccess.Data.Common.OADataReader = cmd.ExecuteReader()
                    Dim categories As IEnumerable(Of Category) = ctx.Translate(Of Category)(reader)
                    reader.NextResult()
                    Dim products As IEnumerable(Of Product) = ctx.Translate(Of Product)(reader)
                End Using
            End Using
        End Using
    End Sub
End Module