I am developing a WPF application .NET 6. I am using Entity Framework with an Oracle 10g database.

Packages:

• EntityFramework 6.5.1.
• Oracle.EntityFrameworkCore 6.21.61.
• Oracle.ManagedDataAccess.EntityFramework 23.7.0
• Oracle.ManagedDataAccess.Core 23.7.0

Models are created using associations.

A normal SELECT query using the Entity Framework is formed this way, and there are no problems:

SELECT "r"."JOB_ID" K0, "r"."CATALOG_ID" K1, "r"."JOB_CODE" K2, "r"."LENGTH" K3, "r"."JOB_SEQ" K4, "r"."JOB_SP" K5, "r"."JOB_STATUS" K6, "r"."JOB_WEIGHT" K7, "r"."OPERATOR" K8, "r"."OPERATOR_MEMO" K9, "r"."PLANNED_DATE" K10, "r"."REQUIRED_WEIGHT" K11, "r"."REVISION" K12, "r"."TASK_JOB_ID" K13, "r0"."CATALOG_ID" K14, "r0"."BILLET_TYPE_ID" K15, "r0"."CATALOG_CODE" K16, "r0"."OPERATOR" K17, "r0"."PRODUCT_TYPE_ID" K18, "r0"."REVISION" K19, "r0"."STEEL_GRADE_IN" K20, "r0"."STEEL_GRADE_OUT" K21, "r1"."STEEL_GRADE_ID" K22, "r1"."ALTERNATIVE_GRADE_CODE" K23, "r1"."DESCRIPTION" K24, "r1"."OPERATOR" K25, "r1"."REVISION" K26, "r1"."STEEL_GRADE_CODE" K27, "r2"."PRODUCT_TYPE_ID" K28, "r2"."OPERATOR" K29, "r2"."PRODUCT_TYPE_CODE" K30, "r2"."PROFILE_ID" K31, "r2"."REVISION" K32
FROM "RML_JOB" "r"
LEFT JOIN "RML_CATALOG" "r0" ON ("r"."CATALOG_ID" = "r0"."CATALOG_ID")
LEFT JOIN "RML_STEEL_GRADE" "r1" ON ("r0"."STEEL_GRADE_IN" = "r1"."STEEL_GRADE_ID")
LEFT JOIN "RML_PRODUCT_TYPE" "r2" ON ("r0"."PRODUCT_TYPE_ID" = "r2"."PRODUCT_TYPE_ID")
WHERE ("r"."JOB_STATUS" = 30)

When calling UPDATE, such as this:

UPDATE "TEST_TABLE" SET "COLUMN1" = :p0 WHERE "COLUMN2" = :p1;

An error occurs:

PLS-00103: Encountered the symbol "" when expecting one of the following: begin function package pragma procedure subtype type use <идентификатор> <идентификатор с двойными кавычками-разделителями> form current cursor

When executing this query in SQL Developer in the same schema, the error does not appear.

I tried downgrading the package versions to 2.19.xx, but they only support .NET Core 3.1.

Can you tell me what the problem might be? It is desirable to keep .NET 6.

I am developing a WPF application .NET 6. I am using Entity Framework with an Oracle 10g database.

Packages:

• EntityFramework 6.5.1.
• Oracle.EntityFrameworkCore 6.21.61.
• Oracle.ManagedDataAccess.EntityFramework 23.7.0
• Oracle.ManagedDataAccess.Core 23.7.0

Models are created using associations.

A normal SELECT query using the Entity Framework is formed this way, and there are no problems:

SELECT "r"."JOB_ID" K0, "r"."CATALOG_ID" K1, "r"."JOB_CODE" K2, "r"."LENGTH" K3, "r"."JOB_SEQ" K4, "r"."JOB_SP" K5, "r"."JOB_STATUS" K6, "r"."JOB_WEIGHT" K7, "r"."OPERATOR" K8, "r"."OPERATOR_MEMO" K9, "r"."PLANNED_DATE" K10, "r"."REQUIRED_WEIGHT" K11, "r"."REVISION" K12, "r"."TASK_JOB_ID" K13, "r0"."CATALOG_ID" K14, "r0"."BILLET_TYPE_ID" K15, "r0"."CATALOG_CODE" K16, "r0"."OPERATOR" K17, "r0"."PRODUCT_TYPE_ID" K18, "r0"."REVISION" K19, "r0"."STEEL_GRADE_IN" K20, "r0"."STEEL_GRADE_OUT" K21, "r1"."STEEL_GRADE_ID" K22, "r1"."ALTERNATIVE_GRADE_CODE" K23, "r1"."DESCRIPTION" K24, "r1"."OPERATOR" K25, "r1"."REVISION" K26, "r1"."STEEL_GRADE_CODE" K27, "r2"."PRODUCT_TYPE_ID" K28, "r2"."OPERATOR" K29, "r2"."PRODUCT_TYPE_CODE" K30, "r2"."PROFILE_ID" K31, "r2"."REVISION" K32
FROM "RML_JOB" "r"
LEFT JOIN "RML_CATALOG" "r0" ON ("r"."CATALOG_ID" = "r0"."CATALOG_ID")
LEFT JOIN "RML_STEEL_GRADE" "r1" ON ("r0"."STEEL_GRADE_IN" = "r1"."STEEL_GRADE_ID")
LEFT JOIN "RML_PRODUCT_TYPE" "r2" ON ("r0"."PRODUCT_TYPE_ID" = "r2"."PRODUCT_TYPE_ID")
WHERE ("r"."JOB_STATUS" = 30)

When calling UPDATE, such as this:

UPDATE "TEST_TABLE" SET "COLUMN1" = :p0 WHERE "COLUMN2" = :p1;

An error occurs:

PLS-00103: Encountered the symbol "" when expecting one of the following: begin function package pragma procedure subtype type use <идентификатор> <идентификатор с двойными кавычками-разделителями> form current cursor

When executing this query in SQL Developer in the same schema, the error does not appear.

I tried downgrading the package versions to 2.19.xx, but they only support .NET Core 3.1.

Can you tell me what the problem might be? It is desirable to keep .NET 6.

• c#
• entity-framework-core
• oracle10g
• -6.0

• Please share a minimal reproducible example. – mjwills Commented Apr 2 at 5:20

1 Answer 1

The decision was prompted by this discussion:

https://github/dbeaver/dbeaver/issues/2785?ysclid=m916v6muad787128353

When removing CRLF from a query in DBeaver, the problem disappeared.

I enabled logging in the console and looked at the generated queries. And in fact the queries were formed with CRLF. Although it did not prevent selectfrom doing so.

To solve it, I found a command interceptor and registered it in dbContext and it worked.

DbContext code:

var loggerFactory = LoggerFactory.Create(builder =>
            {
                builder.AddConsole().AddDebug().AddFilter(DbLoggerCategory.Database.Command.Name, LogLevel.Information);
            });

            optionsBuilder.UseOracle(connStr, options => options.UseOracleSQLCompatibility("11"))
                .AddInterceptors(new NewlineRemovingInterceptor())
                .UseLoggerFactory(loggerFactory)
                .EnableSensitiveDataLogging();

Interceptor code (also for asynchronous operations):

public class NewlineRemovingInterceptor : DbCommandInterceptor
{
    private static string RemoveNewlines(string sql)
    {
        return sql.Replace("\r\n", " ")
                  .Replace("\n", " ")
                  .Replace("\r", " ");
    }

    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<int> result)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.NonQueryExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.ReaderExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<object> result)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.ScalarExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<object> result,
        CancellationToken cancellationToken = default)
    {
        command.CommandText = RemoveNewlines(command.CommandText);
        return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }
}