Open Closed

Oracle boolean issue! #9797

User avatar
0
jizhongqi created

Hello friends，

I got the latest abp.io framework, and connect to oracle 19, it failed to migrate the abp initial database. I got the same qestion as descript in the post:https://abp.io/support/questions/9453/efcore-booloracle--19c-not-boolean-type-abp-vnext-oracle--bool-type-error

then I follow the post, to solve the issue of table AbpBackgroundJobs change the boolean column to number(10), then I got thee erro from next table which has boolean properties. as show:

so my question is, since abp support oracle, I suppose the abp should support oracle out of the box. but now we have change the code to adapt the code to support oracle. and actualy don't know to fix the issue, and it amy bring other issues later,

please help how can I sovle the issue?
Go to accepted answer
14 Answer(s)
  • User Avatar
    0
    AI-Bot created
    Support Team ABP Agent

    • The issue occurs because Oracle does not support the native BOOLEAN type in table columns. When ABP/EF Core tries to create tables with BOOLEAN columns, Oracle throws an error (ORA-00902: invalid datatype).

    • To resolve this, you must map boolean properties to a supported Oracle type, such as NUMBER(1) or NUMBER(10), instead of BOOLEAN.

    • ABP provides guidance for Oracle EF Core integration. You should use the Devart provider and follow the official documentation to handle type mapping issues.

    • Steps to fix:

      1. In your DbContext, override the OnModelCreating method to map all boolean properties to NUMBER(1) or NUMBER(10).
      2. Example:
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (property.ClrType == typeof(bool) || property.ClrType == typeof(bool?))
                {
                    property.SetColumnType("NUMBER(1)");
                }
            }
        }
    }
    1. Remove any existing migrations that use BOOLEAN, re-create the migrations, and update the database.

    This approach will allow your ABP application to work with Oracle without manual table edits for every boolean property.

    This response was generated by AI. If it didn’t solve your problem, feel free to post a new answer — our support team will jump in to help you soon.

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    你可以给所有使用bool属性的实体进行配置

    这些方法会被所有实体调用 https://abp.io/support/questions/9453/efcore-booloracle--19c-not-boolean-type-abp-vnext-oracle--bool-type-error#answer-3a1a70d6-0e4f-4435-da9e-a3590b6ef483

  • User Avatar
    0
    jizhongqi created

    你好，按照上面AI的提示修改后 生成的immigration代码如下 执行数据库迁移，还是报错。

    请帮看看哪里有问题呢？

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    完整的错误/异常信息?

  • User Avatar
    0
    jizhongqi created

    2025-08-21 17:22:24.459 +08:00 [INF] Started database migrations... 2025-08-21 17:22:26.240 +08:00 [ERR] 2025-08-21 17:22:26.240666 ThreadID:11 (ERROR) OracleRelationalCommand.ExecuteNonQueryAsync() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "FALSE": 标识符无效 ORA-06512: 在 line 2 https://docs.oracle.com/error-help/db/ora-00904/ at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQueryAsync(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, OracleConnection connection, ENQ_RefAndOutParamArgCtx enq_refOutArgCtx, Boolean isFromEF, Boolean bAsync) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryInternalAsync(Boolean bAsync, CancellationToken cancellationToken) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryAsyncHelper(CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) 2025-08-21 17:22:26.252 +08:00 [ERR] Failed executing DbCommand (40ms) [Parameters=[], CommandType='"Text"', CommandTimeout='0'] BEGIN EXECUTE IMMEDIATE 'CREATE TABLE "AbpBackgroundJobs" ( "Id" RAW(16) NOT NULL, "ApplicationName" NVARCHAR2(96), "JobName" NVARCHAR2(128) NOT NULL, "JobArgs" NCLOB NOT NULL, "TryCount" NUMBER(5) DEFAULT 0 NOT NULL, "CreationTime" TIMESTAMP(7) NOT NULL, "NextTryTime" TIMESTAMP(7) NOT NULL, "LastTryTime" TIMESTAMP(7), "IsAbandoned" NUMBER(1) DEFAULT False NOT NULL, "Priority" NUMBER(3) DEFAULT 15 NOT NULL, "ExtraProperties" NVARCHAR2(2000) NOT NULL, "ConcurrencyStamp" NVARCHAR2(40) NOT NULL, CONSTRAINT "PK_AbpBackgroundJobs" PRIMARY KEY ("Id") )'; END; 2025-08-21 17:22:26.255 +08:00 [ERR] 2025-08-21 17:22:26.255348 ThreadID:11 (ERROR) OracleExecutionStrategy.ExecuteAsync() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "FALSE": 标识符无效 ORA-06512: 在 line 2 https://docs.oracle.com/error-help/db/ora-00904/ at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQueryAsync(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, OracleConnection connection, ENQ_RefAndOutParamArgCtx enq_refOutArgCtx, Boolean isFromEF, Boolean bAsync) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryInternalAsync(Boolean bAsync, CancellationToken cancellationToken) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryAsyncHelper(CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable1 isolationLevel, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable1 isolationLevel, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) 2025-08-21 17:22:26.256 +08:00 [ERR] 2025-08-21 17:22:26.256845 ThreadID:11 (ERROR) OracleExecutionStrategy.ExecuteAsync() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "FALSE": 标识符无效 ORA-06512: 在 line 2 https://docs.oracle.com/error-help/db/ora-00904/ at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQueryAsync(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, OracleConnection connection, ENQ_RefAndOutParamArgCtx enq_refOutArgCtx, Boolean isFromEF, Boolean bAsync) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryInternalAsync(Boolean bAsync, CancellationToken cancellationToken) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryAsyncHelper(CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable1 isolationLevel, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteAsync(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable1 isolationLevel, CancellationToken cancellationToken) at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable1 isolationLevel, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementationAsync(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<<MigrateAsync>b__22_1>d.MoveNext() --- End of stack trace from previous location --- at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    继续把默认值也改一下

    property.SetColumnType("NUMBER(1)");
property.SetDefaultValue(0);
  • User Avatar
    0
    jizhongqi created

    [maliming] said: 继续把默认值也改一下

    property.SetColumnType("NUMBER(1)"); 
property.SetDefaultValue(0);

    不行哦，更新完成，然后重新生产代码后还是false。

  • User Avatar
    0
    jizhongqi created

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    试试property.SetDefaultValueSql("0");?

  • User Avatar
    0
    jizhongqi created

    add-migration直接报错了

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    请通过https://wetransfer.com/ 分享一个模版项目 liming.ma@volosoft.com

  • User Avatar
    0
    jizhongqi created

    [maliming] said: 请通过https://wetransfer.com/ 分享一个模版项目
    liming.ma@volosoft.com

    您好，项目文件已经邮件发出，请查收

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    好的 我会尽快检查它

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    Solution: UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19)

Assignee
User avatar maliming
Question details
Labels
None yet
Boost Your Development
ABP Live Training
Packages
See Trainings
Mastering ABP Framework Book
The Official Guide
Mastering
ABP Framework
Learn More
Mastering ABP Framework Book
Made with ❤️ on ABP v10.0.0-preview. Updated on September 01, 2025, 08:37