Oracle to Databricks: function mapping
4 of 8 common Oracle functions need a change in Databricks. Mapping table with Databricks syntax and behavior differences.
Other targets: Oracle to Snowflake · Oracle to BigQuery
Needs a rewrite (4)
No Databricks function with the same behavior; use the construct shown.
| Oracle | Databricks | Notes |
|---|---|---|
| DUAL | Rewrite SELECT expr | The FROM clause is optional in a SELECT. Sequences read with seq.NEXTVAL FROM DUAL need a different mechanism in each target (Snowflake sequences, identity columns in Databricks, GENERATE_UUID or ROW_NUMBER in BigQuery). |
| ROWNUM | Rewrite LIMIT n / row_number() OVER (ORDER BY ...) | QUALIFY is supported as well. Oracle assigns ROWNUM before ORDER BY, so WHERE ROWNUM <= 10 ... ORDER BY x returns 10 arbitrary rows sorted afterwards. Decide whether the old query meant that or "top 10 by x" before translating it to LIMIT. |
| SYSDATE | Rewrite current_timestamp() | getdate() and now() are synonyms. Use current_date() for the date only. Oracle SYSDATE returns the operating system time of the database server. If that server ran in a local time zone, expressions such as TRUNC(SYSDATE) mean "today in local time" and will shift by hours in a UTC warehouse. Oracle date arithmetic such as SYSDATE - 1 (one day earlier) does not carry over to Databricks or BigQuery; use dateadd or DATETIME_SUB with an explicit unit. |
| TO_CHAR | Rewrite date_format(expr, 'yyyy-MM-dd HH:mm:ss') | Java-style patterns: 'YYYY-MM-DD HH24:MI:SS' becomes 'yyyy-MM-dd HH:mm:ss'. to_char also accepts dates in Databricks Runtime 14.1 and above, with the same patterns. Oracle MI (minutes) and MM (month) map to mm and MM in Databricks. Copying the Oracle model unchanged produces wrong output instead of an error in many cases. Oracle fill mode (FM) and NLS parameters for month names have no exact equivalent; check any format that uses FM, MONTH or DAY with known dates. TO_CHAR on numbers with format models such as '999,999.00' also differs per target; test it separately from date formats. |
Works as written (4)
The same call runs, but check the notes for behavior differences.
| Oracle | Databricks | Notes |
|---|---|---|
| ADD_MONTHS | Same name add_months(startDate, numMonths) | Returns a DATE. Clamps to the end of the month only when the day does not exist. Oracle ADD_MONTHS(DATE '2022-02-28', 1) returns 2022-03-31 because Feb 28 is the last day of February. Databricks and BigQuery return 2022-03-28. Month-end schedules (billing, accruals) are where this shows up. |
| DECODE | Same name decode(expr, key1, value1 [, ...] [, defValue]) | NULL matches NULL. Not to be confused with decode(bin, charset), which decodes binary data. |
| LISTAGG | Same name listagg([ALL | DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Databricks SQL and Databricks Runtime 16.4 and later. On older runtimes use array_join(collect_list(expr), delimiter), which does not guarantee order. ON OVERFLOW TRUNCATE and ON OVERFLOW ERROR are Oracle only; remove the clause and check the target size limit instead. |
| NVL | Same name nvl(expr1, expr2) | Synonym of coalesce(expr1, expr2). Oracle stores '' as NULL, so NVL(col, 'n/a') also replaces empty strings there. In the targets an empty string is not NULL; use NVL(NULLIF(col, ''), 'n/a') to keep the Oracle result. Oracle NVL converts expr2 to the type of expr1. The targets use a common type of both arguments, which can change numeric precision or string length in the result. |