MapToStoredProcedures Simple Test

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mawilliams
Posts: 3
Joined: Thu 06 Feb 2014 00:17

MapToStoredProcedures Simple Test

Post by mawilliams » Thu 06 Feb 2014 00:27

I'm attempting to use the MapToStoredProcedures support with EF6 and dotConnect (8.2.90.6) and have been unable to get it to work. I consistently receive "ORA-04043: object XXX does not exist" error where XXX may be the user name or the name of the stored procedure to create.

Here's is the simple code I am using to test with:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DevArtEFTest
{
  class Program
  {
    static void Main(string[] args)
    {
      Database.SetInitializer(new DropCreateDatabaseAlways<DevArtContext>());

      var drone = new Drone
      {
        Name = "Test Drone"
      };

      using (var ctx = new DevArtContext())
      {
        ctx.Drones.Add(drone);
        ctx.SaveChanges();
      }

      if (System.Diagnostics.Debugger.IsAttached)
      {
        Console.Write("Any key to terminate... ");
        Console.ReadKey(true);
      }
    }
  }

  public class Drone
  {
    public int DroneId { get; set; }
    public string Name { get; set; }
  }

  public class DevArtContext : DbContext
  {
    public DbSet<Drone> Drones { get; set; }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.HasDefaultSchema("DEMO");
      
      modelBuilder.Entity<Drone>().Property(p => p.Name).HasMaxLength(100);

      modelBuilder.Entity<Drone>().MapToStoredProcedures();
    }
  }
}
When I use HasDefaultSchema the error is (the name of the user):

ORA-04043: object DEMO does not exist

If I comment out the HasDefaultSchema setting the error is (the name of the first of the three stored procedures to create):

ORA-04043: object "Drone_Insert" does not exist

Can anyone advise as to what I am doing wrong here?

Would any additional information be helpful?

Regards.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: MapToStoredProcedures Simple Test

Post by Shalex » Thu 06 Feb 2014 13:18

Generally, we recommend using the dbMonitor tool for tracing database activity. This helps to identify the reasons of problems.
http://www.devart.com/dotconnect/oracle ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe
mawilliams wrote:ORA-01918: user 'DEMO' does not exist
There is a difference in the meaning of schemas between SQL Server and Oracle: http://stackoverflow.com/questions/5365 ... oft-schema. Have you created the DEMO user in Oracle database before running your code?

The DEMO user also can be created automatically:

Code: Select all

    var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
    config.DatabaseScript.Schema.DeleteDatabaseBehaviour = Devart.Data.Oracle.Entity.Configuration.DeleteDatabaseBehaviour.Schema;
    modelBuilder.HasDefaultSchema("DEMO");
But this creates the "__MigrationHistory" table in the schema specified in connection string instead of the schema set in .HasDefaultSchema(). We are investigating the issue.

As a workaround, please comment the line of code modelBuilder.HasDefaultSchema("DEMO");. After this, the code will work with schema (user) specified in connection string.
mawilliams wrote:ORA-04043: object "Drone_Insert" does not exist
The problem is caused by the code modelBuilder.Entity<Drone>().MapToStoredProcedures(); (we will fix behaviour to generate NotImplemented exception). If you comment this line, the code will work.

Could you please specify the reason(s) why you are using the .MapToStoredProcedures() functionality in EF Code-First (instead of "common" insert/update/delete SQL statements)? We did not support this feature in our EF-provider because found no reason to generate the stored procedures automatically instead of creating them manually in database.

mawilliams
Posts: 3
Joined: Thu 06 Feb 2014 00:17

Re: MapToStoredProcedures Simple Test

Post by mawilliams » Thu 06 Feb 2014 16:36

Hi,

Thanks for the update. Perhaps I should have mentioned in my original post that I am familiar with Oracle. I am, however, not familiar with Entity Framework so I was doing some walkthrough exercises to learn more about Entity Framework. These exercises are for SQL Server but I wanted to compare against Oracle so that is the genesis of the use of MapToStoredProcedures - it came from a SQL Server walkthrough that I wanted to try against Oracle.

This is not my quote:
mawilliams wrote:ORA-01918: user 'DEMO' does not exist
It seems the text has been changed. The error is NOT ORA-01918. The user definitely exists as that is the user I've specified in the connection string in the App.config file.

The text as I originally posted is the text from the exception. The text from the inner exception with the stack trace is:

Code: Select all

ORA-04043: object "DEMO" does not exist
   at Devart.Data.Oracle.a1.c(Int32 A_0)
   at Devart.Data.Oracle.aq.e(Int32 A_0)
   at Devart.Data.Oracle.aq.a(String A_0, Int32 A_1)
   at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
   at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
   at Devart.Common.DbCommandBase.CreateSql()
   at Devart.Common.DbCommandBase.get_Sql()
   at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.h.a(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.h.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.Mapping.Update.Internal.FunctionUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
In any case, you've answered my question in that MapToStoredProcedures is not implemented. I thought I had read that it was, but it seems I have misunderstood that as now I can't find the document where I thought I had read that!

Thanks again for your quick reply!

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: MapToStoredProcedures Simple Test

Post by Shalex » Fri 07 Feb 2014 09:08

mawilliams wrote:This is not my quote:
mawilliams wrote:ORA-01918: user 'DEMO' does not exist
Sorry for that. It was the error I have encountered with your project.
mawilliams wrote:ORA-04043: object "DEMO" does not exist
We could not reproduce this error. Please enable the dbMonitor tool and specify the exact SQL statement sent to database when the error occurs.

mawilliams
Posts: 3
Joined: Thu 06 Feb 2014 00:17

Re: MapToStoredProcedures Simple Test

Post by mawilliams » Fri 07 Feb 2014 14:45

I enabled DbMonitor as well as Oracle trace (10046, level 4) and there is no error in either. Since this appears to occur as a result of "Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)" call, perhaps the code is sending a "describe packet" (i.e. equivalent of OCIDescribeAny call) and not a traceable SQL statement?

Also, given this only happens with MapToStoredProcedures, and it's already been established that MapToStoredProcedures is not supported, I'd say it's a curiousity, but perhaps not unexpected. If there's additional information you would like, let me know and I can try to provide.

Shalex
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: MapToStoredProcedures Simple Test

Post by Shalex » Thu 13 Feb 2014 18:02

mawilliams wrote:But this creates the "__MigrationHistory" table in the schema specified in connection string instead of the schema set in .HasDefaultSchema(). We are investigating the issue.
The following code should be added in the project to change the schema for the __MigrationHistory table:

Code: Select all

  using System.Data.Common;
  using System.Data.Entity;
  using System.Data.Entity.Migrations.History;

  public class MyHistoryContext : HistoryContext {

      public MyHistoryContext(DbConnection existingConnection, string defaultSchema)
        : base(existingConnection, defaultSchema) {
      }
  }

  public class MyConfiguration : DbConfiguration {

    public MyConfiguration() {

      SetHistoryContext("Devart.Data.Oracle", (conn, schema) => new MyHistoryContext(conn, "DEMO"));
    }
  }

Post Reply