How to Execute Ref Cursor Proc in Oracle via EF

skingaby · Post by **skingaby** » Wed 24 Jun 2009 14:55

I have a Proc:

PROCEDURE Get_Confirm_Document(
      DealID IN deal.DEAL_ID%TYPE,
      r_cursor OUT sys_refcursor 
   ) 
   IS

I added it to the EF Model as a Function, and it generated this code:

Code: Select all

public global::System.Data.Objects.ObjectResult GetConfirmDocument(global::System.Nullable dEALID, global::System.Data.Objects.ObjectParameter r_CURSOR)
        {
            global::System.Data.Objects.ObjectParameter dEALIDParameter;
            if (dEALID.HasValue)
            {
                dEALIDParameter = new global::System.Data.Objects.ObjectParameter("DEALID", dEALID);
            }
            else
            {
                dEALIDParameter = new global::System.Data.Objects.ObjectParameter("DEALID", typeof(long));
            }
            return base.ExecuteFunction("GetConfirmDocument", dEALIDParameter, r_CURSOR);
        }

And I created a Unit Test to execute the proc and return the results.

Code: Select all

[TestMethod]
        public void MainPdfProcTest()
        {
            var context = new ServerModelEF.GcsModelEntities();
            var result = context.GetConfirmDocument((long?)7623, null);
            Assert.IsNotNull(result);
        }

What should the GetConfirmDocument() call pass in as the second parameter? It doesn't like the null. I get an error: System.InvalidOperationException: The parameter at index 1 in the parameters array is null.

Thanks.

AndreyR · Post by **AndreyR** » Thu 25 Jun 2009 09:06

Thank you for the report.
We plan to significantly improve support for reference cursors in the upcoming build.
As a temporary workaround you can change the direction of the cursor parameter to Input in the Store part of the model,
and then recreate the method in the conceptual model, setting the return type for it to the type of entity the procedure returns.

skingaby · Post by **skingaby** » Tue 07 Jul 2009 13:46

I tried reversing the direction of that parameter and it still doesn't work. I cannot seem to fake it out any way I try. I am anxiously awaiting the next release as there are several features that I am looking forward to.

AndreyR · Post by **AndreyR** » Wed 15 Jul 2009 06:26

The new build is available.

AndreyR · Post by **AndreyR** » Wed 29 Jul 2009 07:57

Please refer to the new tutorial here:
http://www.devart.com/blogs/dotconnect/?p=5