I created an external connection to an Oracle Database 12c Standard Edition Release 12.1.0.2.0.
The connection is working, I executed successfully a lot of queries with different filters and joins. But I couldn't find a way to make any date filter pushdown work. When I try to run a simple query with a date filter I get this error:
select dt_updated
from external_conn.my_schema.my_table
where dt_updated > cast('2026-01-01' as date)
ORA-17041: Missing IN or OUT parameter at index: 1 https://docs.oracle.com/error-help/db/ora-17041/
The "dt_updated" is a timestamp field and databricks recognizes it when I see the catalog.
I tried a lot of different syntaxes:
- where dt_updated > '2024-01-01'
- where dt_updated > DATE '2024-01-01'
- where dt_updated > TIMESTAMP('2024-01-01 00:00:00')
- where dt_updated > TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
- where dt_updated > TIMESTAMP '2024-01-01 00:00:00'
- where dt_updated > CAST('2024-01-01 00:00:00' AS TIMESTAMP)
- where dt_updated > TO_DATE('2024-01-01', 'YYYY-MM-DD')
But all of them compile to the same external engine query, that I can see when I run EXPLAIN FORMATTED:
External engine query:
SELECT "DT_UPDATED"
FROM "MY_SCHEMA"."MY_TABLE"
WHERE ("DT_UPDATED" IS NOT NULL)
AND ("DT_UPDATED" > 2026-01-01T00:00:00Z)
Looks like the engine is not parsing correctly =/
I tried """datediff(dt_updated, cast('2026-01-01' as date)) > 0""" and the query run, but the performance is terrible because the table is too large and this function doesn't pushdown to oracle.
Am I doing something wrong?
Here is the oracle federated queries docs:
https://docs.databricks.com/aws/en/query-federation/oracle
