Issue with Oracle stored procedure returning SYS_REFCURSOR and Entity Framework Model

c# entity-framework-6 oracle oracle-manageddataaccess


I have an Oracle database (11.2 server-side with 12.1 client bits) with loads of stored procedures that return a SYS_REFCURSOR object type.

I tried to import this set of procedures through the Add new ADO.NET Entity Data Model wizard within Visual Studio 2017 (15.6.3) and the Oracle Managed DataAccess client (12.1). But when I access these stored procedures I get an exception such as the following: PLS-00306: wrong number or types of arguments in call to 'my_procedure'

An example of such an Oracle stored procedure looks like the following:

create or replace PROCEDURE my_procedure (
       input               IN  VARCHAR2,
       cur_output OUT SYS_REFCURSOR
    OPEN cur_output FOR
    SELECT    col1
    ,    col2
    ,    col3
    FROM my_table
    WHERE col1 = input;
        WHEN OTHERS THEN    
END my_procedure;

The generated model xml looks like:

<Function Name="my_procedure" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="myschema">
    <Parameter Name="input" Type="varchar2" Mode="In" />

Generated C# code looks like the following:

public virtual int my_procedure(string input) {
    var inputParameter = input != null ?
        new ObjectParameter("input", input) :
        new ObjectParameter("input", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("my_procedure", inputParameter);

I played a bit with manually calling the Oracle DataAccess APIs and the following code works:

OracleConnection conn = new OracleConnection(...);  
using (OracleCommand cmd = new OracleCommand("my_procedure", conn)) {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("input", OracleDbType.Varchar2).Value = "myinput";                
    cmd.Parameters.Add("cur_output", OracleDbType.RefCursor, null, ParameterDirection.Output);
    var dr = cmd.ExecuteReader();
    var col1id = dr.GetOrdinal("col1");
    var valuecol1 = SafeGetString(dr, col1id);

Ideally I would like to have an automated process to generate the correct C# code to access the stored procedures. What is the most practical way to fix this?

3/25/2018 7:46:16 PM

Popular Answer

You define your stored procedure as my_procedure with two arguments, input and cur_output. When you call this procedure in C#, you should also pass a parameter FOR BOTH input and cur_output. A procedure is not a function wherein it returns a value. Procedure will assign the output to the variable that you assigned as the OUT parameter, which is in this case named, cur_output.

3/25/2018 8:18:45 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow