It is possible with Code First approach to call any Oracle Procedure (not Function) without result value(s)?

[J.Gall]

Hi,

Question: It is possible with Code First approach to call any Oracle Procedure without result value(s)? All Examples I found was with Functions with result data.

My Example:

Used NuGet packages:
EntityFramework v6.1.1
EntityFramework – Support for store functions for Entity Framework 6.1.1+ code First

Used dotConnect from Oracle v8.4.264.6

Code: Select all

  using System;
  using System.Data.Common;
  using System.Data.Entity;
  using System.Data.Entity.Infrastructure;  
  using CodeFirstStoreFunctions;
  using System.Data.Entity.Core.Objects;

    public class DatabaseModelContainer : DbContext
    {
        static DatabaseModelContainer()
        {
            Database.SetInitializer<DatabaseModelContainer>(null);
        }

        public DatabaseModelContainer()
            : base("Name=DatabaseModelContainer")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
           …

          modelBuilder.Conventions.Add(new FunctionsConvention<DatabaseModelContainer>("dbo"));
        }

    [DbFunction("DatabaseModelContainer", "SET_PROD_QUANTITY")]
    public ObjectResult<int> SET_PROD_QUANTITY(Nullable<decimal> i_ID, Nullable<decimal> i_POS)
    {
        var i_IDParameter = i_ID.HasValue ?
            new ObjectParameter("I_ID", i_ID) :
            new ObjectParameter("I_ID", typeof(decimal));

        var i_POSParameter = i_POS.HasValue ?
            new ObjectParameter("I_POS", i_POS) :
            new ObjectParameter("I_POS", typeof(decimal));

        return (((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>(" SET_PROD_QUANTITY", i_IDParameter, i_POSParameter));
    }

Call for SET_PROD_QUANTITY has work, procedure in Oracle DB was executed, but with Exception: System.Data.Entity.Core.EntityCommandExecutionException: The data reader returned by the store data provider does not have enough columns for the query requested. This exception is correct, we have no return columns in result. Procedures in Oracle DB don’t return any value or result (expect out Parameter in procedures, or function - "CREATE OR REPLACE FUNCTION ... RETURN INTEGER"). We have not found any Example equivalent to ObjectContext.ExecuteFunction without result values by Code First approach.


Example procedure:

Code: Select all

CREATE OR REPLACE PROCEDURE dbo.SET_PROD_QUANTITY (I_ID IN  NUMBER, I_POS IN NUMBER) IS
BEGIN
  BEGIN
    DECLARE

      D_PROD_ID    VARCHAR2 (15);
      D_QUANTITY   NUMBER (8, 3);
      D_CNT        NUMBER (5);

    BEGIN
      SELECT COUNT (*) INTO D_ANZ FROM PROD_DATA
        WHERE ID = I_ID AND POS = I_POS;

      IF D_CNT = 1 THEN
        SELECT PROD_ID, QUANTITY INTO D_PROD_ID, D_QUANTITY
          FROM PROD_DATA
          WHERE ID = I_ID AND POS = I_POS;

        UPDATE PRODUCT
           SET QUANTITY_IN  = NVL (QUANTITY_IN, 0) - D_ QUANTITY,
               QUANTITY_OUT = NVL (QUANTITY_OUT, 0) + D_QUANTITY
         WHERE ID = D_PROD_ID;
      END IF;
    END;
  END;
END;

Regards
Jaroslav

[Shalex]

Please try this code:

Code: Select all

    using (DatabaseModelContainer context = new DatabaseModelContainer())
    {
        // var a = context.SET_PROD_QUANTITY(1, 1);
        var I_ID = new OracleParameter("I_ID", OracleDbType.Number);
        var I_POS = new OracleParameter("I_POS", OracleDbType.Number);
        I_ID.Value = 1;
        I_POS.Value = 1;
        context.Database.ExecuteSqlCommand("begin SET_PROD_QUANTITY(:I_ID, :I_POS); end;", I_ID, I_POS);
    }