oracle-databaseentity-framework-5sys-refcursor

How to pass Oracle Ref_Cursor as output to the Entity Framework


I have a Oracle package in which there is a procedure:

PROCEDURE pGetData(pi_dStartDate IN  date, 
                           pi_dEndDate   IN  date, 
                           po_curData    OUT sys_refcursor,
                           po_nStatus    OUT NUMBER,
                           po_sErrorText OUT VARCHAR2)

As you can see there is an sys_refcursor output parameter. I have gone through the following steps mentioned in the link: EntityFrameworkOBE to add the procedure with Entityframwork.

But when I "Add function" from Model browser, I get only 4 parameters to pass in the function in DBContext generated code.

        public virtual ObjectResult<pGetData_Result> ProcGetData
        (Nullable<System.DateTime> pI_DSTARTDATE,
         Nullable<System.DateTime> pI_DENDDATE,
         ObjectParameter pO_NSTATUS,
         ObjectParameter pO_SERRORTEXT)
    {
        var pI_DSTARTDATEParameter = pI_DSTARTDATE.HasValue ?
            new ObjectParameter("PI_DSTARTDATE", pI_DSTARTDATE) :
            new ObjectParameter("PI_DSTARTDATE", typeof(System.DateTime));

        var pI_DENDDATEParameter = pI_DENDDATE.HasValue ?
            new ObjectParameter("PI_DENDDATE", pI_DENDDATE) :
            new ObjectParameter("PI_DENDDATE", typeof(System.DateTime));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<pGetData_Result>
            ("pGetData", pI_DSTARTDATEParameter, pI_DENDDATEParameter, pO_NSTATUS, pO_SERRORTEXT);
    }

I cannot pass the "Ref_Cursor", due to which I am getting error:

"ORA-06550: line 1, column 8:\nPLS-00306: wrong number or types of arguments in call to 'PGETUTILTSINTILEDATA'\nORA-06550: line 1, column 8:\nPL/SQL: Statement ignored"

Please help.

Thanks.


Solution

  • This is the correct approach that I was trying. The only issue because of which the code was not returning the value was that I had made a separate class Library for my EDMX file.

    To solve this:

    Go to properties of the EDMX file and set the "Copy to Output Directory" to "Copy Always".

    In case if you have included the EDMX file in the same directory of your main executable application than there will not be any issue and you can perform the same approach that I have defined above.