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

How to: Execute Oracle Stored Procedures Returning RefCursors

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

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. In order to create a new domain model based on a Oracle database, you have to install the ODP.NET Driver from Oracle. You can download it from here.

Suppose, you have created a domain model that looks like:

Also, you need to add a reference to the Oracle.DataAccess.dll assembly. By default the assembly is located in the following directory: C:\app\{UserName}\product\11.2.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll

Executing Stored Procedures

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

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

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.

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 (EntitiesModel ctx = new EntitiesModel())
           {
               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 EntitiesModel()
            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 (EntitiesModel ctx = new EntitiesModel())
           {
               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 EntitiesModel()
            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