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

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

Postby J.Gall » Fri 21 Nov 2014 10:03

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
J.Gall
 
Posts: 7
Joined: Fri 21 Nov 2014 08:41

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

Postby Shalex » Tue 25 Nov 2014 08:35

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);
    }
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle