Calling a spelling procedure with an EF error

Question

I have an oracle storage procedure that takes 2 lines and a date in the input parameter and which give the ref cursor as output:

CREATE OR REPLACE PROCEDURE SCHEMA.MYPROSTO ( pPl IN VARCHAR2, -- Comma (;) separated pTy IN VARCHAR2,-- Comma (;) separated pDate IN mytable.mydate%TYPE, pCursor OUT sys_refcursor) IS ..... sSQL VARCHAR2 (3000); BEGIN -- making SQL Order sSQL := 'SELECT TO_CHAR (v.date_c........ ...... OPEN pCursor FOR sSQL; END MYPROSTO; 

The output cursor returns a set of three rows of string cells.

I imported this stored procedure in my entity framework model, with this in the .config file:

 <oracle.manageddataaccess.client> <version number="*"> <implicitRefCursor> <storedProcedure schema="SCHEMA" name="MYPROSTO"> <refCursor name="PCURSOR"> <bindInfo mode="Output"/> <metadata columnOrdinal="0" columnName="YEAR" providerType="Varchar2" allowDBNull="true" nativeDataType="Varchar2"/> <metadata columnOrdinal="1" columnName="MONTH" providerType="Varchar2" allowDBNull="true" nativeDataType="Varchar2"/> <metadata columnOrdinal="2" columnName="COUNT" providerType="Varchar2" allowDBNull="true" nativeDataType="Varchar2"/> </refCursor> </storedProcedure> </implicitRefCursor> </version> </oracle.manageddataaccess.client> 

The import wizzard function created a result object and generated an access function:

 public virtual ObjectResult<MYPROSTO_Result> MYPROSTO (string pPL, string pTY, Nullable<System.DateTime> pDATE) { var pPLParameter = pPL!= null ? new ObjectParameter("PPL", pPL) : new ObjectParameter("PPL", typeof(string)); var pTYParameter = pTY!= null ? new ObjectParameter("PTY", pTY) : new ObjectParameter("PTY", typeof(string)); var pDATEParameter = pDATE.HasValue ? new ObjectParameter("PDATE", pDATE) : new ObjectParameter("PDATE", typeof(System.DateTime)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<MYPROSTO_Result>("MYPROSTO", pPLParameter, pTYParameter, pDATEParameter); } 

However, calling this function throws an exception ( System.Data.Entity.Core.EntityCommandExecutionException ) in the last line:

 ORA-06550: Ligne 1, colonne 8 : PLS-00306: wrong number or types of arguments in call to 'MYPROSTO' ORA-06550: Ligne 1, colonne 8 : PL/SQL: Statement ignored 

I don't understand why he is failing

Answer 1

There are several problems:

• Submit varchar (2) in a date field
• Parameter name "pTY" versus "pType"
• Parameter name "pPL" versus "pPlant"
• Parameter name "PPLT" versus "PPL"

Answer 2

How about a return parameter. Is this "displayed" normal? Doublecheck

Answer 3

Of course, the error message is clear:

"invalid number or argument types when calling" MYPROSTO "

Your procedure expects 4 parameters, but you will skip only 3. You need an output variable for refcursor.

Answer 4

I had the same thing with me, and FINALLY decided. I used oracle.manageddataaccess.client, just like you, and my .NET solution was divided into a presentation project, a WebAPI project, and a data access project. My app.config in the data access project had the correct ImplicitRefCursor section with cursor definition and metadata, just like you did, but I forgot to copy it to my web.config of my WebAPI project as well. As soon as I did this, the error of the “wrong number or argument types” disappeared. Hope this helps. (I just copied the entire oracle.manageddataaccess.client section.)

Also, if you generated this entire ImplicitRefCursor section manually, I found a much simpler and more reliable way to do this. From the .NET Server Explorer, connect to your database, find your stored procedure, right-click it and run RUN. Then OK. It will display a list of IN and OUT parameters, including the cursor. If you click the Show Config button, it will show you what should be in your EF app.config. The AddConfig button will add it for you. This helps to avoid mistakes.