Working with Oracle REF CURSORs and Stored Procedures is an often used mechanism when working with Oracle data sources. REF CURSORS, as a PL/SQL data type offers a pointer-like address that is an effect a memory location of a query address space. The address space is much like a standard result set (or DataSet in .NET parlance), but it allows you deal with results of query on the Oracle server. You find more details here and here .
With what I like to call vanilla ADO.NET using either the OracleCommand or DbCommand objects, you’ve a few choices in how to deal with REF CURSORs depending on how much control you want from your application.You can deal with REF CURSORs ‘implicitly’ in which case its not necessary to set your stored procedures parameters specifically using a specialized description. This can be a nice way of supporting a more diverse set of stored proc calls in a smaller code base. With the explicit REF CURSOR support you’ve more control as you can specify the OracleDbType.RefCusor description to tag your stored proc parameters objects as ref cursors. All of these are configurable via the ‘Ref Cursor Mode’ connection string option.
With EF, we approach the stored procedure and REF Cursor challenge by defaulting to the ‘implicit’ mode – this maximizes the portability of your EDM across multiple data stores, which as I discussed earlier.
Take the following as an example. First we’ll create the package then procedure within this package which we will use with EF; the SQL syntax follows below:
First create a package to define our own datatype cursorType as ref cursor.
AS
type cursorType IS ref cursor;
END;
Next, create Stored Procedure using the cursorType defined above
AS
BEGIN
open myCursor FOR SELECT * FROM "Categories" WHERE "Categories"."CategoryID" = CATEGORYID;
END;
With the procedure is created, we can generate, but in this case we will create a SSDL map to start the process of wiring this into our EDM. Obviously either the EDMGen tool or Visual Studio Designer will do this for you.
<Function Name="f_CategoriesGet1_" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName=""CategoriesGet1"" Schema="ADOVS6">
<Parameter Name="CATEGORYID" Type="number" Mode="In" />
<Parameter Name="MYCURSOR" Type="ref cursor" Mode="Out" />
<Parameter Name="CATEGORYNAME" Type="char" Mode="In" />
</Function>
For our conceptual map, we can’t rely on the tooling to do this for so some manual intervention is required. Take particular note that the REF Cursor parameter is bound as Type=”Binary”.
<EntityContainer Name="Entities">
<FunctionImport Name="GetCategories" EntitySet="Categories" ReturnType="Collection(Model.Categories)">
<Parameter Name="CATEGORYID" Mode="In" Type="Decimal" />
<Parameter Name="MYCURSOR" Mode="Out" Type="Binary" />
<Parameter Name="CATEGORYNAME" Mode="In" Type="String" />
</FunctionImport>
</EntityContainer>
And of course the MSL file to wire our schema and conceptual models together.
<EntityContainerMapping StorageEntityContainer="ModelStoreContainer" CdmEntityContainer="Entities">
<FunctionImportMapping FunctionImportName="GetCategories" FunctionName="Model.Store.f_CategoriesGet1_">
</FunctionImportMapping>
</EntityContainerMapping>
That’s it!
Now you are ready use your to write an application to call these procedure, and it should be of course accessible from EntitySQL, ObjectServices or my own personal favorite, LINQ.
com.CommandType = System.Data.CommandType.StoredProcedure;
com.Parameters.Add("CategoryID", DbType.Int32).Value = 1;
com.Parameters.Add("CategoryName", DbType.AnsiString).Value = "Beverages";
EntityDataReader rdr = com.ExecuteReader(CommandBehavior.SequentialAccess);
With ObjectServices
/// There are no comments for Model.GetCategories in the schema.
/// </summary>
public global::System.Data.Objects.ObjectResult<Categories> GetCategories(global::System.Nullable<decimal> cATEGORYID, string cATEGORYNAME)
{
global::System.Data.Objects.ObjectParameter cATEGORYIDParameter;
if (cATEGORYID.HasValue)
{
CATEGORYIDParameter = new global::System.Data.Objects.ObjectParameter("CATEGORYID", cATEGORYID);
}
else
{
CATEGORYIDParameter = new global::System.Data.Objects.ObjectParameter("CATEGORYID", typeof(decimal));
}
global::System.Data.Objects.ObjectParameter cATEGORYNAMEParameter;
if ((CATEGORYNAME != null))
{
CATEGORYNAMEParameter = new global::System.Data.Objects.ObjectParameter("CATEGORYNAME", cATEGORYNAME);
}
else
{
CATEGORYNAMEParameter = new global::System.Data.Objects.ObjectParameter("CATEGORYNAME", typeof(string));
}
return base.ExecuteFunction<Categories>("GetCategories", cATEGORYIDParameter, cATEGORYNAMEParameter);
}
And finally LINQ….
var cq = ent.GetCategories(1, "Beverages");
Categories c = cq.First();
Many thanks to Avadhoot Kulkarni and Jayakhanna Pasimuthu for doing the heavy lifting for this posting!
{ 2 comments… read them below or add one }
Nice work. Thanks.
I couldn’t get this to work. A couple of things were confusing.
* In your stored procedure, you had two arguments, CATEGORYID IN number, myCursor OUT types.cursorType, yet your usage examples use an integer and a string, but no cursor.
* I don’t understand how to actually fix the issue when “some manual intervention is required”
I get to the point where I am making the LINQ call, but it’s asking for me to pass a parameter for the cursor, and I don’t know what to do. Your examples seem to hint that the cursor parameter is ignorable.
Any suggestions?