ODP.NET

Below are examples of incorrectly generated SQL queries. Version 8.23 generates SQL queries correctly.

Example generated by version 9.23:

DECLARE 
l_sql     varchar2(32767);
l_cur     pls_integer;
l_execute pls_integer;

BEGIN
l_cur := dbms_sql.open_cursor;
l_sql := 'SELECT "w0"."ID" "Id", "l"."ID" "Id", RTRIM(LTRIM(RTRIM(CONCAT(COALESCE("l"."OPENING_NOTE", TO_CLOB('''')), N''; '', COALESCE("l"."CLOSING_NOTE", TO_CLOB(''''))))), N'';'') "Text", CAST("l"."MY_DATA_NUMBER" AS VARCHAR2(20)) "ShortText"
FROM "SCOTT"."MY_DATAS" "l"
INNER JOIN "SCOTT"."MY_DATAS" "l0" ON "l"."ID" = "l0"."ID"
INNER JOIN (
    SELECT "w"."ID"
    FROM "SCOTT"."WORKS" "w"
    WHERE (((("w"."DELETED_DATE_TIME" IS NULL) AND ("w"."FACILITY_ID" = :facilityId_0))) AND ("w"."ID" = :id_1))
) "w0" ON "l0"."WORK_ID" = "w0"."ID"';
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ':facilityId_0', 1);
dbms_sql.bind_variable(l_cur, ':id_1', 2606);
l_execute:= dbms_sql.execute(l_cur);
dbms_sql.return_result(l_cur);
END;

And valid SQL generated from version 8.23:

DECLARE 
l_sql     varchar2(32767);
l_cur     pls_integer;
l_execute pls_integer;

BEGIN
l_cur := dbms_sql.open_cursor;
l_sql := 'SELECT "t"."ID", "l"."ID", "l"."OPENING_NOTE", "l"."CLOSING_NOTE", CAST("l"."MY_DATA_NUMBER" AS VARCHAR2(20))
FROM "SCOTT"."MY_DATAS" "l"
INNER JOIN "SCOTT"."MY_DATAS" "l0" ON "l"."ID" = "l0"."ID"
INNER JOIN (
    SELECT "w"."ID"
    FROM "SCOTT"."WORKS" "w"
    WHERE (((("w"."DELETED_DATE_TIME" IS NULL) AND ("w"."FACILITY_ID" = :facilityId_0))) AND ("w"."ID" = :id_1))
) "t" ON "l0"."WORK_ID" = "t"."ID"';
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ':facilityId_0', 1);
dbms_sql.bind_variable(l_cur, ':id_1', 419);
l_execute:= dbms_sql.execute(l_cur);
dbms_sql.return_result(l_cur);
END;

I also noticed a problem when generating SQL from the expression "!(x.IsEnabled)". An invalid NOT ("w". "IS_ENABLED") expression is generated.

Enviroment:

• dotnet 8
• Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0

• builder.Services.AddDbContext<MyDbContext>(options =>
      options.UseOracle(connectionString, opt =>
      {
          opt.UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19);
      }));