Oracle to BigQuery: function mapping
8 of 8 common Oracle functions need a change in BigQuery. Mapping table with BigQuery syntax and behavior differences.
Other targets: Oracle to Snowflake · Oracle to Databricks
Needs a rewrite (6)
No BigQuery function with the same behavior; use the construct shown.
| Oracle | BigQuery | Notes |
|---|---|---|
| ADD_MONTHS | Rewrite DATE_ADD(date, INTERVAL n MONTH) | 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 | Rewrite CASE expr WHEN search1 THEN result1 ... ELSE default END | A simple CASE never matches NULL; add WHEN expr IS NULL THEN ... first. DECODE(status, NULL, 'none', ...) becomes CASE WHEN status IS NULL THEN 'none' ... END. Translating it to CASE status WHEN NULL silently never matches. |
| DUAL | Rewrite SELECT expr | A SELECT without FROM returns one row. 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_DATETIME([time_zone]) | DATETIME in UTC unless a time zone is passed. 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 FORMAT_DATE('%Y-%m-%d', date) / FORMAT_DATETIME('%Y-%m-%d %H:%M:%S', dt) | Use the FORMAT_* function that matches the input type. 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. |
Different name (2)
Same behavior under another name; rename the call and check the notes.
| Oracle | BigQuery | Notes |
|---|---|---|
| LISTAGG | Different name STRING_AGG([DISTINCT] expr [, delimiter] [ORDER BY ...]) | ORDER BY goes inside the parentheses. Default delimiter is a comma. ON OVERFLOW TRUNCATE and ON OVERFLOW ERROR are Oracle only; remove the clause and check the target size limit instead. WITHIN GROUP is not accepted; move the ORDER BY inside STRING_AGG(...). |
| NVL | Different name IFNULL(expr, null_result) | COALESCE also works. 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. |