Base solution for your next web application
Open Closed

Problem - Working with Oracle #2669


User avatar
0
VuCA created

Dear Asp.Net Zero Team,

Does asp.net zero working with oracle database, I can't find find MigrationSqlGenerator for oracle managed data access.

I just found the document asp.net zero work with MySql, please provide for me the solution in this situtiation.

[http://aspnetboilerplate.com/Pages/Documents/EF-MySql-Integration])

Thank you in advance!


9 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    What happens when you skip the part for adding MigrationSqlGenerator ? Did you try in that way ?

    Thanks.

  • User Avatar
    0
    VuCA created

    <cite>ismcagdas: </cite> Hi,

    What happens when you skip the part for adding MigrationSqlGenerator ? Did you try in that way ?

    Thanks.

    I already tried by skip adding MigrationSqlGenerator but It did not work. These are some steps I done with no luck.

    1. Install Oracle.ManagedDataAccess.Client.EntityFramework from nuget package to my .EntityFramework and .Web projects
    2. Change the default connectionstring to Oracle Database in web.config
    3. Clean all the migration default
    4. Re-generate migrations by using command: Add-Migration "AbpZero_Initial"
    5. Update database by using: Update-Database

    The errors below:

    PM> Update-Database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying explicit migrations: [201703131204550_AbpZero_Initial]. Applying explicit migration: 201703131204550_AbpZero_Initial. Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01918: user 'dbo' does not exist at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.

    Thanks for your support!

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    Can you try solution offered here <a class="postlink" href="http://stackoverflow.com/questions/27250555/oracle-manageddataaccess-entityframework-ora-01918-user-dbo-does-not-exist">http://stackoverflow.com/questions/2725 ... -not-exist</a> ?

    It seems like you need to set schema name in OnModelCreating.

  • User Avatar
    0
    VuCA created

    <cite>ismcagdas: </cite> Hi,

    Can you try solution offered here <a class="postlink" href="http://stackoverflow.com/questions/27250555/oracle-manageddataaccess-entityframework-ora-01918-user-dbo-does-not-exist">http://stackoverflow.com/questions/2725 ... -not-exist</a> ?

    It seems like you need to set schema name in OnModelCreating.

    It's work when set default schema in OnModelCreating but another error occurs when update-database

    create table "KHTT"."AbpNotifications" ( "Id" raw(16) not null, "NotificationName" nvarchar2(96) not null, "Data" nclob null, "DataTypeName" nvarchar2(512) null, "EntityTypeName" nvarchar2(250) null, "EntityTypeAssemblyQualifiedName" nvarchar2(512) null, "EntityId" nvarchar2(96) null, "Severity" number(3, 0) not null, "UserIds" nclob null, "ExcludedUserIds" nclob null, "TenantIds" nclob null, "CreationTime" date not null, "CreatorUserId" number(19, 0) null, constraint "PK_AbpNotifications" primary key ("Id") ) Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00972: identifier is too long at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e() at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.

    How to deal with it?

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    It seems like Oracle does not allow a column name longer than 30 chars. You need to map the column name "EntityTypeAssemblyQualifiedName" to a shorter name instead.

  • User Avatar
    0
    VuCA created

    <cite>ismcagdas: </cite> Hi,

    It seems like Oracle does not allow a column name longer than 30 chars. You need to map the column name "EntityTypeAssemblyQualifiedName" to a shorter name instead.

    Hi,

    I know the error occurs is column EntityTypeAssemblyQualifiedName 32 characters and it used in AbpNotifications, AbpNotificationSubscriptions, AbpTenantNotifications --> but I did not find where is it define in .Core project.

    Please tell me how to fix?

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    It is defined in ABP Framework here <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/blob/master/src/Abp/Notifications/NotificationSubscriptionInfo.cs#L43">https://github.com/aspnetboilerplate/as ... nfo.cs#L43</a>.

    So you need to add a code line something like this in your DbContext's OnModelCreating.

    modelBuilder.Entity<NotificationSubscriptionInfo>().Property(e => e.EntityTypeAssemblyQualifiedName).HasColumnName("EntityTypeAssemblyQn");
    
  • User Avatar
    0
    VuCA created

    <cite>ismcagdas: </cite> Hi,

    It is defined in ABP Framework here <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/blob/master/src/Abp/Notifications/NotificationSubscriptionInfo.cs#L43">https://github.com/aspnetboilerplate/as ... nfo.cs#L43</a>.

    So you need to add a code line something like this in your DbContext's OnModelCreating.

    modelBuilder.Entity<NotificationSubscriptionInfo>().Property(e => e.EntityTypeAssemblyQualifiedName).HasColumnName("EntityTypeAssemblyQn");
    

    Hi, I found 2 column exeed max length of oracle column. There are:

    • Column EntityTypeAssemblyQualifiedName in NotificationSubscriptionInfo, TenantNotificationInfo, NotificationInfo tables
    • Column ShouldChangePasswordOnNextLogin in User table Thank ismcagdas very much!
  • User Avatar
    0
    ismcagdas created
    Support Team

    I'm happy that it is working for you :).