Oracle to Snowflake: function mapping
2 of 8 common Oracle functions need a change in Snowflake. Mapping table with Snowflake syntax and behavior differences.
Other targets: Oracle to Databricks · Oracle to BigQuery
Needs a rewrite (2)
No Snowflake function with the same behavior; use the construct shown.
| Oracle | Snowflake | Notes |
|---|---|---|
| DUAL | Rewrite SELECT expr | The FROM clause is optional. DUAL is not described in the Snowflake SQL reference, so do not rely on it. 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 ROW_NUMBER() OVER (...) <= n filters on the window result without a subquery. 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. |
Works as written (6)
The same call runs, but check the notes for behavior differences.
| Oracle | Snowflake | Notes |
|---|---|---|
| ADD_MONTHS | Same name ADD_MONTHS(date_or_timestamp_expr, num_months_expr) | If the input is the last day of a month, the result is the last day of the result month, as in Oracle. Snowflake DATEADD(month, ...) does not apply the last-day rule; only ADD_MONTHS does. Keep ADD_MONTHS when the Oracle behavior matters. |
| DECODE | Same name DECODE(expr, search1, result1 [, ...] [, default]) | A NULL expr matches a NULL search value, as in Oracle. |
| LISTAGG | Same name LISTAGG([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Ignores NULL values; default delimiter is an empty string. 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) | Alias of IFNULL. 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. |
| SYSDATE | Same name SYSDATE() | Returns TIMESTAMP_NTZ in UTC, not a DATE. Parentheses are required. CURRENT_TIMESTAMP returns local session time instead. 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. Bare SYSDATE without parentheses is an error in Snowflake. 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 | Same name TO_CHAR(expr [, 'format']) | Format elements are close to Oracle: YYYY, MM, DD, HH24, MI, SS, MON, DY. 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. |