Oracle Direct Query text filter causes visual crash

[BogdanL]

Hello Community,

as suggested by @lbendlin here [Oracle Direct Query text filter causes visual crash] i'm opening this issue.

Recap:

I am encountering a critical error when trying to search using a text filter in a Direct Query report connected to an Oracle 11g database.

My issue appears similar to the one described in the thread: Searching a slicer in my Direct Query report blows... - Microsoft Fabric Community.

Upon investigation, I found that the Power BI Oracle connector generates an incorrect SQL query, causing the error. Below are the details of my environment:

• Power BI Desktop version: 2.138.1004.0 64-bit (November 2024)
• Oracle Driver: Oracle Client for Microsoft Tools (OCMT) x64
• Oracle version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64-bit Production

The error I receive is: ORA-00904: "t4"."CIG": invalid identifier.

This error arises from the UPPER("t4"."CIG") condition in the SQL WHERE clause generated by Power BI, which is deeply nested in subqueries and leads to runtime issues.

DAX Generated by Power BI:

VAR __DS0FilterTable = FILTER(
    KEEPFILTERS(VALUES('V_G4_LOTTI'[CIG])),
    SEARCH("ZY", 'V_G4_LOTTI'[CIG], 1, 0) >= 1
)

VAR __DS0Core = CALCULATETABLE(
    VALUES('V_G4_LOTTI'[CIG]), 
    KEEPFILTERS(__DS0FilterTable)
)

VAR __DS0PrimaryWindowed = TOPN(101, __DS0Core, 'V_G4_LOTTI'[CIG], 1)

EVALUATE __DS0PrimaryWindowed
ORDER BY 'V_G4_LOTTI'[CIG]

SQL Generated by Power BI Connector:

SELECT * 
FROM (
    SELECT
        "t4"."CIG"
    FROM 
    ((
        SELECT 
            "_"."ID_INTERNO_LOTTO"  AS "ID_INTERNO_LOTTO",
            "_"."CIG"               AS "CIG",
            "_"."OGGETTO_LOTTO"     AS "OGGETTO_LOTTO"
        FROM "GGAP3_PREP"."V_G4_LOTTI" "_"
    )) "t4"
    WHERE 
    (
        (
            SELECT INSTR(Op2, Op1, Op3) 
            FROM (
                SELECT UPPER('ZY') AS Op1, 
                       UPPER("t4"."CIG") AS Op2, 
                       1 AS Op3 
                FROM DUAL
            ) AuxTable
        ) >= 1
    )
    GROUP BY "t4"."CIG"
    ORDER BY "t4"."CIG" ASC
) WHERE ROWNUM <= 101

The invalid SQL seems to stem from the reference to UPPER("t4"."CIG") in the WHERE clause. Since "t4"."CIG" is nested within the subqueries, Oracle does not recognize it at runtime.

I would greatly appreciate any insights.

Thank you in advance for your help!

[Anonymous]

Hi  @BogdanL .

Based on the above information, It's a reproducible issue. But since community engineers no longer have a direct feedback channel with the PG team. I would suggest opening a Support Ticket.

If you are a Power BI Pro or Fabric licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you. 
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[BogdanL]

Hello @Anonymous 

Internally within the company, I conducted tests on two other systems: Oracle Database 18c and 19c. In these cases, searching a field in Direct Query works as expected. Based on this, I conclude that the issue lies with Oracle 11g. Given the age of its engine, dating back to 2007, it likely cannot handle complex context scopes.

I have opened a ticket with Power BI Support and was contacted by an engineer. Together, we agreed on an alternative solution: incremental refresh by loading of 10 years of archived history, combined with updates for the most recent day every 30 minutes from 7:30 AM to 7:30 PM.

Best Regards,

BogdanL