Effective EF with Oracle (Stored Procedures with REF CURSORs)

Effective EF with Oracle (Stored Procedures with REF CURSORs)

June 26, 2009 0 Comments

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.

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. [cc lang="sql" line_numbers="false"] CREATE OR REPLACE PACKAGE types AS type cursorType is ref cursor; END; [/cc] Next, create Stored Procedure using the cursorType defined above [cc lang="sql" line_numbers="false"] CREATE OR REPLACE procedure "CategoriesGet" (CATEGORYID IN number, myCursor OUT types.cursorType) AS BEGIN open myCursor for SELECT * FROM "Categories" Where "Categories"."CategoryID" = CATEGORYID; END; [/cc]

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.

[cc lang="xml" line_numbers="false"]


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

[cc lang="xml" line_numbers="false"]

[/cc] And of course the MSL file to wire our schema and conceptual models together. [cc lang="xml" line_numbers="false"]


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. [cc lang="csharp" line_numbers="false"] com.CommandText = "Entities.GetCategories"; 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); [/cc]

With ObjectServices [cc lang="csharp" line_numbers="false"] ///

/// There are no comments for Model.GetCategories in the schema. ///


public global::System.Data.Objects.ObjectResult GetCategories(global::System.Nullable 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("GetCategories", cATEGORYIDParameter, cATEGORYNAMEParameter); } [/cc]

And finally LINQ….

[cc lang="csharp" line_numbers="false"] Entities ent = new Entities(); var cq = ent.GetCategories(1, "Beverages"); Categories c = cq.First(); [/cc]

Many thanks to Avadhoot Kulkarni and Jayakhanna Pasimuthu for doing the heavy lifting for this posting!

Jonathan Bruce

View all posts from Jonathan Bruce on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.

Comments are disabled in preview mode.
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

More From Progress
ProgressNEXT: Premier Event for Modern Application Development
Read More
Getting Started with Kinvey
Read More
Low-Code Platforms: What Developers Think and Why
Read More