oracledb sql error

[erdemcer]

Bug description

Hi,
I have installed superset 5 via docker compose . Installed cx_Oracle last version and Oracle instant_client . For Oracle database 11g sqllab gives folling error :

oracle error: ORA-00933: SQL command not properly ended

This error does not occur on db which is 19c. Problem is coming from following sql

SELECT
1 AS col1
FROM dual
FETCH FIRST 11 ROWS ONLY

"FETCH FIRST 11 ROWS ONLY" clause is not valid under version Oracle 12. Is there any workaround in order to execute sql on superset 5 on Oracle db 11g.

Thanks.

Screenshots/recordings

No response

Superset version

5.0.0

Python version

3.11

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

The error happens because Oracle 11g doesn't support the FETCH FIRST N ROWS ONLY clause—this syntax was introduced in Oracle 12c. Superset 5, via SQLAlchemy and its Oracle engine spec, generates this clause for row limiting, and there isn't a configuration option or documented workaround to switch to the older ROWNUM-based syntax required by Oracle 11g. This logic is hardcoded in Superset's Oracle engine spec and SQLAlchemy dialects, and there is no version-specific handling for Oracle 11g in Superset 5.0.0 [source].

Workarounds are limited: you could manually rewrite queries to use WHERE ROWNUM <= N, but Superset-generated queries (like those from the UI or charts) will still use the unsupported syntax. Supporting Oracle 11g would require customizing or monkey-patching the OracleEngineSpec class to generate compatible SQL, but this is not exposed by a simple config or connection parameter and would require code changes [source].

As of Superset 5.0.0, there is no official or documented solution for this. If you need full compatibility with Oracle 11g, you would need to implement a custom engine spec or patch the SQL generation logic.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[msyavuz]

I am afraid the bot is correct here, you can probably add some code to the engine spec in superset/db_engine_specs/oracle.py to remedy this. I am happy to review and push it forward if you decide to open a pr. Bol şans!