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

user5716448

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

Alex Keh-Oracle

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

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

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

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

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: \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

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

SSakthi-Oracle

use 18.3 version .dll files which is available in Nuget