entity framework core - beta ORA-00933: SQL command not properly ended due to fetch first 1 rows onl — oracle-tech

    Forum Stats

  • 3,716,134 Users
  • 2,242,961 Discussions
  • 7,845,841 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

entity framework core - beta ORA-00933: SQL command not properly ended due to fetch first 1 rows onl

user5716448
user5716448 Member Posts: 1,683 Silver Badge
edited May 2019 in ODP.NET

Hi,

managed to change our solution from sql server to oracle and can now read oracle database table

Using https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/?view=aspnetcore-2.2

when select delete or edit details get command ORA-00933: SQL command not properly ended

Any thoughts on how to resolve? looks like ef core generating FETCH FIRST 1 ROWS ONLY on sql for some reason - how can this be stopped?

SELECT "m"."MULT_MULTIPLE_CODE", "m"."MULT_ANMW_MULTIPLE_CODE", "m"."MULT_AUTH_REQUIRED", "m"."MULT_JMW_MULTIPLE_TYPE_CODE", "m"."MULT_LINK_MULTIPLE_CODE", "m"."MULT_NAME", "m"."MULT_PROM_AUTH_REQUIRED", "m"."MULT_SELECTED_MULTIPLE"

FROM "MULTIPLE" "m"

WHERE "m"."MULT_MULTIPLE_CODE" = :id_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)

Have got the Create working.

It looks like the Edit and Delete have the

FETCH FIRST 1 ROWS ONLY extra bit added which only available from 12g onwards by look of it.

Can the sql not be generated without FETCH FIRST 1 ROWS ONLY as this looks superfluous to me as the equality predicate in our case is enough as it is a primary key.?

Thanks

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    Set the extension method UseOracleSQLCompatibility("11"). This will ensure Oracle 11g SQL is used instead of 12+. If you're not familiar with this extension method, please consult the beta doc.

    user5716448
  • user5716448
    user5716448 Member Posts: 1,683 Silver Badge
    edited April 2019

    Thanks for update.  Do you have a link to the beta doc? - had a look at github and nuget and can't seem to find the document.

  • user5716448
    user5716448 Member Posts: 1,683 Silver Badge
    edited April 2019

    Managed to get it working on local iis in visual studio connecting to database , opt => opt.UseOracleSQLCompatibility("11") in the connection

    If could supply the link to the beta doc that would be great

    Also getting when copy the solution to web server - server can see the database and write permission on folder and have .net framework 4 on server

    403 - Forbidden: Access is denied.

    You do not have permission to view this directory or page using the credentials that you supplied.

    Any thoughts on why getting this?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited April 2019

    If you unzip the NuGet package, the beta doc is the PDF in the base directory. The beta doc is also installed by NuGet to the following directory: C:\Users\<username>\.nuget\packages\Oracle.EntityFrameworkCore\2.18.0-beta3\

    The 403 error is usually due to not configuring the correct the default IIS page or the application pool identity not having website root directory permissions.

    user5716448
  • user5716448
    user5716448 Member Posts: 1,683 Silver Badge
    edited May 2019

    Thanks again for advice.

    Have got past the forbidden message.

    Had to change the appsettings.json file to point to the database as was pointing at the local one.

    But get message below now

    Error.

    An error occurred while processing your request.

    Request ID: 0HLME1V180M3A:00000001

    Development Mode

    Swapping to Development environment will display more detailed information about the error that occurred.

    Development environment should not be enabled in deployed applications, as it can result in sensitive information from exceptions being displayed to end users. For local debugging, development environment can be enabled by setting the ASPNETCORE_ENVIRONMENT environment variable to Development, and restarting the application.

    tried amending web.config to set to Development but then get

    500 - Internal server error.

         <aspNetCore processPath="dotnet" arguments=".\RazorPagesMultiple.dll" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout"

    <environmentVariables>

        <environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Development" />

      </environmentVariables>

    />

    Had a look at the stdout and get below.

    Hosting environment: Production

    Content root path: D:\netcorepublished

    Now listening on: http://127.0.0.1:12539

    Application started. Press Ctrl+C to shut down.

    warn: Microsoft.AspNetCore.HttpsPolicy.HttpsRedirectionMiddleware[3]

          Failed to determine the https port for redirect.

    fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]

          An error occurred using the connection to database '' on server '<SID>.WORLD'.

    Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out

       at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

       at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

       at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)

       at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

       at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)

    Have below in appsettings.json

    {

        "Logging": {

            "LogLevel": {

                "Default": "Warning"

            }

        },

        "AllowedHosts": "*",

        "ConnectionStrings": {

            "RazorPagesMultipleContext": "Data Source=(DESCRIPTION=(ADDRESS=(COMMUNITY=<SID>.WORLD)(HOST=<server>)(PORT=1521))(CONNECT_DATA=(SID=<SID>)(GLOBAL_NAME=<SID>.WORLD)));User Id=<uname>;Password=<pwd>;"

        }

    }

    any thoughts on why not connecting - other .net apps on database can connect to this database.

  • user5716448
    user5716448 Member Posts: 1,683 Silver Badge
    edited May 2019

    Will start a new thread  as past original forbidden message as concerns solution working locally but not when deployed

  • SSakthi-Oracle
    SSakthi-Oracle Member Posts: 16 Employee
    edited May 2019

    use 18.3 version .dll files which is available in Nuget

    user5716448
Sign In or Register to comment.