Hi,

we are using LLBLGen 5.10.1 (Adapter), net4.8/net6.0. Our database is Oracle 11/19 and our target .net version 4.8/netstandard2.0.

We found a problem with PrefetchPaths on relation properties which are a self reference. If we have such a case a query is generated which contains an alias (in the where subquery) with the name "__LLBLPP". Unfortunately this name seems not to be compatible with Oracle identifier naming rules: See https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA point 6. This means that a nonquoted identifier cannot start with a "_".

You can reproduce this with a simple LINQ query like this one (where RequiredOrderDetail points again to a different OrderDetail):

new LinqMetaData(adpater).OrderDetail.With(o => o.RequiredOrderDetail)

results in a sql query like this:

SELECT ....
FROM   SL_ORDERDETAIL
WHERE  (SL_ORDERDETAIL.ORDERDETAILID IN
        (SELECT __LLBLPP.REQUIREDORDERDETAILID AS RequiredOrderDetailID
         FROM   SL_ORDERDETAIL __LLBLPP)) 

With this query I get an ORA-00911 "invalid character" error at the "__LLBLPP" alias.
Can you help us here?

Best regards,
Markus

Do you have OracleCaseSensitiveNames set to false by any chance?

Please try the following (originally for field aliases). Set the AlwaysWrapFieldAliasesInDoubleQuotes flag to true.

The PostgreSQL and Oracle ODP.NET DQE's now have a static flag, available in the DynamicQueryEngine class: AlwaysWrapFieldAliasesInDoubleQuotes which is false by default, and which can be used to make the DQE always wrap aliases in double quotes, even if CaseInsensitiveNames is set to true. Use this to avoid having aliases which are reserved words to cause query failure when CaseInsensitiveNames is set to true.

Ok, now this is interesting. I just recognized that I have indeed set the CaseInsensitiveNamesFlag to true in my LinqPad LLBLGen test query but forgot about it it (shame on me). Without it (CaseInsensitiveNamesFlag is false, name are case sensitive) the query is successful without the ORA-00911 error.

However, when I leave the CaseInsensitiveNamesFlag on true and set AlwaysWrapFieldAliasesInDoubleQuotes to true to prevent the error, the query changes, but unfortunately not all alias occurrences are wrapped and therefore the error still appears. With CaseInsensitiveNamesFlag = true & AlwaysWrapFieldAliasesInDoubleQuotes = true the above query looks like this:

SELECT ...
FROM   SL_ORDERDETAIL
WHERE  (SL_ORDERDETAIL.ORDERDETAILID IN
        (SELECT __LLBLPP.REQUIREDORDERDETAILID AS "RequiredOrderDetailID"
         FROM   SL_ORDERDETAIL "__LLBLPP")) 

As you can see, the alias declaration in the from clause is wrapped correctly, but the usage in the select clause is not.

We'll look into it

(edit) It seems to occur with a relationship to self. Normal fetches work fine, but a relationship with self gets a special alias. Odd that it isn't wrapped tho...

Tho the two settings are a bit two sides of the same coin: you can't have case insensitive names when the names are always wrapped in quotes, because Oracle sees a wrapped name as the exact name, so "Foo" is different from "foo" but without quotes, Foo is equal to foo.

I can't reproduce it on 5.11.1, with AlwaysWrap set to true and CaseInsensitiveNames set to false:

SELECT "SCOTT"."EMP"."COMM"     AS "Comm",
       "SCOTT"."EMP"."DEPTNO"   AS "Deptno",
       "SCOTT"."EMP"."EMPNO"    AS "Empno",
       "SCOTT"."EMP"."ENAME"    AS "Ename",
       "SCOTT"."EMP"."HIREDATE" AS "Hiredate",
       "SCOTT"."EMP"."JOB"      AS "Job",
       "SCOTT"."EMP"."MGR"      AS "Mgr",
       "SCOTT"."EMP"."SAL"      AS "Sal"
FROM   "SCOTT"."EMP"
WHERE  ("SCOTT"."EMP"."MGR" IN
        (SELECT "__LLBLPP"."EMPNO" AS "Empno"
         FROM   "SCOTT"."EMP" "__LLBLPP")) 

Will try the other setups / 5.10

It fails with CaseInsensitiveNames set to true and AlwaysWrapFieldAliasesInDoubleQuotes to false (default). This is due to the issue with the artificial alias __LLBLPP...

SELECT SCOTT.EMP.COMM     AS "Comm",
       SCOTT.EMP.DEPTNO   AS "Deptno",
       SCOTT.EMP.EMPNO    AS "Empno",
       SCOTT.EMP.ENAME    AS "Ename",
       SCOTT.EMP.HIREDATE AS "Hiredate",
       SCOTT.EMP.JOB      AS "Job",
       SCOTT.EMP.MGR      AS "Mgr",
       SCOTT.EMP.SAL      AS "Sal"
FROM   SCOTT.EMP
WHERE  (SCOTT.EMP.MGR IN
        (SELECT __LLBLPP.EMPNO AS "Empno"
         FROM   SCOTT.EMP "__LLBLPP")) 

I think it's best if we simply always wrap an alias that starts with _ if it's not already wrapped, as oracle can't deal with it.

Exactly. Normally we use case sensitive names (CaseInsensitiveNames =false), so there is no problem for our code in general.

I only had CaseInsensitiveNames to true in my LinqPad script to true for testing purposes and there this bug occured.

Fixed in the hotfix builds 5.10.3 and 5.11.1

Nice! It works now. Thanks!