Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Asp.Net Core Api project Oracle.EntityFrameworkCore error?

user8203173Aug 22 2019 — edited Aug 22 2019

I am using ASP.NET CORE 2.2 to test the api with the oracle 11.2 HR schema

I using API to get the Region list to display

lsiOracle01.PNG

However, I got error when display single region:

lsiOracle02.PNG

It is very strange error. How can the SQL command not properly ended.

my Region Controller is as following:

namespace Lsi.Api.Controllers

{

\[Route("api/region")\]

\[ApiController\]

public class RegionController : ControllerBase

{

    private readonly OracleDbContext \_context;

    public RegionController (OracleDbContext context)

    {

        \_context = context;

    }

    // GET: api/Region    // **This method works fine**

    \[HttpGet\]

    public async Task\<ActionResult\<IEnumerable\<Region>>> GetRegions()

    {

        return await \_context.Regions.ToListAsync();

    }

    // GET: api/Region/1             // **This method does not work**   

    \[HttpGet("{id}", Name = "GetRegion")\]

    public async Task\<ActionResult\<Region>> GetRegion(int id)

    {

        var region = await \_context.Regions.FindAsync(id);

        if (region == null)

        {

            return NotFound();

        }

        return region;

    }

}

The region Entity:

namespace Lsi.Entity

{

\[Table("REGIONS")\]

public class Region

{

    \[Key, Column("REGION\_ID")\]

    public int Id { get; set; }

    \[Column("REGION\_NAME"), MaxLength(25)\]

    public string RegionName { get; set;}

}

}

The DBContext:

public class OracleDbContext : DbContext

{

    public OracleDbContext(DbContextOptions\<OracleDbContext> options) : base(options) { }

    public DbSet\<Region> Regions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        modelBuilder.HasDefaultSchema("HR");

        modelBuilder.Entity\<Region>().ToTable("REGIONS")

            .HasKey(r => r.Id).HasName("REGION\_ID");

       base.OnModelCreating(modelBuilder);

    }

}

In the start up

I have

    public void ConfigureServices(IServiceCollection services)

    {

        services.AddDbContext\<OracleDbContext>(options =>

        {

            options.UseOracle(Configuration.GetConnectionString("DefaultConnection"));

        });

        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version\_2\_2);

    }

Where is my error? the query in the bold seem work fine whing set the value to 1 when copy and run in DBeaver which connected to Oracle Database.

Is this bug come from Oracle.EntityFrameworkCore?

The error message is as following:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

  Entity Framework Core 2.2.6-servicing-10079 initialized 'OracleDbContext' using provider 'Oracle.EntityFrameworkCore' with options: None

fail: Microsoft.EntityFrameworkCore.Database.Command[0]

  2019-08-23 00:26:22.926388 ThreadID:27  (ERROR)   OracleRelationalCommand.ExecuteAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): **ORA-00933: SQL command not properly ended**

     at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

     at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64\[\] scnForExecution, Int64\[\]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable\`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

     at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

  --- End of stack trace from previous location where exception was thrown ---

     at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary\`2 parameterValues, CancellationToken cancellationToken)

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]

  Failed executing DbCommand (5ms) \[Parameters=\[:get\_Item\_0='?' (DbType = Int32)\], CommandType='Text', CommandTimeout='0'\]

  **SELECT "e"."REGION\_ID", "e"."REGION\_NAME"**

  **FROM "HR"."REGIONS" "e"**

  **WHERE "e"."REGION\_ID" = :get\_Item\_0**

  **FETCH FIRST 1 ROWS ONLY**

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended

at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

--- End of stack trace from previous location where exception was thrown ---

at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)

fail: Microsoft.EntityFrameworkCore.Query[10100]

  An exception occurred while iterating over the results of a query for context type 'Lsi.Data.OracleDbContext'.

  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended

     at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

     at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64\[\] scnForExecution, Int64\[\]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable\`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

     at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

  --- End of stack trace from previous location where exception was thrown ---

     at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary\`2 parameterValues, CancellationToken cancellationToken)

     at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable\`1.AsyncEnumerator.BufferlessMoveNext(DbContext \_, Boolean buffer, CancellationToken cancellationToken)

     at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync\[TState,TResult\](TState state, Func\`4 operation, Func\`4 verifySucceeded, CancellationToken cancellationToken)

     at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable\`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)

     at System.Linq.AsyncEnumerable.FirstOrDefault\_\[TSource\](IAsyncEnumerable\`1 source, CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\First.cs:line 144

     at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.TaskResultAsyncEnumerable\`1.Enumerator.MoveNext(CancellationToken cancellationToken)

     at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator\`2.MoveNextCore(CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\Select.cs:line 106

     at System.Linq.AsyncEnumerable.AsyncIterator\`1.MoveNext(CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\AsyncIterator.cs:line 98

     at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor\`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended

at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

This post has been answered by user8203173 on Aug 22 2019
Jump to Answer

Comments

Post Details

Added on Aug 22 2019
1 comment
4,619 views