Oracle SQL translation guide

This document details the similarities and differences in SQL syntax between Oracle and BigQuery to help you plan your migration. Use batch SQL translation to migrate your SQL scripts in bulk, or interactive SQL translation to translate ad-hoc queries.

Data types

This section shows equivalents between data types in Oracle and in BigQuery.

Oracle	BigQuery	Notes
`VARCHAR2`	`STRING`
`NVARCHAR2`	`STRING`
`CHAR`	`STRING`
`NCHAR`	`STRING`
`CLOB`	`STRING`
`NCLOB`	`STRING`
`INTEGER`	`INT64`
`SHORTINTEGER`	`INT64`
`LONGINTEGER`	`INT64`
`NUMBER`	`NUMERIC`	BigQuery does not allow user specification of custom values for precision or scale. As a result, a column in Oracle may be defined so that it has a bigger scale than BigQuery supports. Additionally, before storing a decimal number Oracle rounds up if that number has more digits after the decimal point than is specified for the corresponding column. In BigQuery this feature could be implemented using `ROUND()` function.
`NUMBER(*, x)`	`NUMERIC`	BigQuery does not allow user specification of custom values for precision or scale. As a result, a column in Oracle may be defined so that it has a bigger scale than BigQuery supports. Additionally, before storing a decimal number Oracle rounds up if that number has more digits after the decimal point than is specified for the corresponding column. In BigQuery this feature could be implemented using `ROUND()` function.
`NUMBER(x, -y)`	`INT64`	If a user tries to store a decimal number, Oracle rounds it up to a whole number. For BigQuery an attempt to store a decimal number in a column defined as `INT64` results in an error. In this case, `ROUND()` function should be applied. BigQuery `INT64` data types allow up to 18 digits of precision. If a number field has more than 18 digits, `FLOAT64` data type should be used in BigQuery.
`NUMBER(x)`	`INT64`	If a user tries to store a decimal number, Oracle rounds it up to a whole number. For BigQuery an attempt to store a decimal number in a column defined as `INT64` results in an error. In this case, `ROUND()` function should be applied. BigQuery `INT64` data types allow up to 18 digits of precision. If a number field has more than 18 digits, `FLOAT64` data type should be used in BigQuery.
`FLOAT`	`FLOAT64`/`NUMERIC`	`FLOAT` is an exact data type, and it's a `NUMBER` subtype in Oracle. In BigQuery, `FLOAT64` is an approximate data type. `NUMERIC` may be a better match for `FLOAT` type in BigQuery.
`BINARY_DOUBLE`	`FLOAT64`/`NUMERIC`	`FLOAT` is an exact data type, and it's a `NUMBER` subtype in Oracle. In BigQuery, `FLOAT64` is an approximate data type. `NUMERIC` may be a better match for `FLOAT` type in BigQuery.
`BINARY_FLOAT`	`FLOAT64`/`NUMERIC`	`FLOAT` is an exact data type, and it's a `NUMBER` subtype in Oracle. In BigQuery, `FLOAT64` is an approximate data type. `NUMERIC` may be a better match for `FLOAT` type in BigQuery.
`LONG`	`BYTES`	`LONG` data type is used in earlier versions and is not suggested in new versions of Oracle Database. `BYTES` data type in BigQuery can be used if it is necessary to hold `LONG` data in BigQuery. A better approach would be putting binary objects in Cloud Storage and holding references in BigQuery.
`BLOB`	`BYTES`	`BYTES` data type can be used to store variable-length binary data. If this field is not queried and not used in analytics, a better option is to store binary data in Cloud Storage.
`BFILE`	`STRING`	Binary files can be stored in Cloud Storage and `STRING` data type can be used for referencing files in a BigQuery table.
`DATE`	`DATETIME`
`TIMESTAMP`	`TIMESTAMP`	BigQuery supports microsecond precision (10^-6) in comparison to Oracle which supports precision ranging from 0 to 9. BigQuery supports a time zone region name from a TZ database and time zone offset from UTC. In BigQuery a time zone conversion should be manually performed to match Oracle's `TIMESTAMP WITH LOCAL TIME ZONE` feature.
`TIMESTAMP(x)`	`TIMESTAMP`	BigQuery supports microsecond precision (10^-6) in comparison to Oracle which supports precision ranging from 0 to 9. BigQuery supports a time zone region name from a TZ database and time zone offset from UTC. In BigQuery a time zone conversion should be manually performed to match Oracle's `TIMESTAMP WITH LOCAL TIME ZONE` feature.
`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP`	BigQuery supports microsecond precision (10^-6) in comparison to Oracle which supports precision ranging from 0 to 9. BigQuery supports a time zone region name from a TZ database and time zone offset from UTC. In BigQuery a time zone conversion should be manually performed to match Oracle's `TIMESTAMP WITH LOCAL TIME ZONE` feature.
`TIMESTAMP WITH LOCAL TIME ZONE`	`TIMESTAMP`	BigQuery supports microsecond precision (10^-6) in comparison to Oracle which supports precision ranging from 0 to 9. BigQuery supports a time zone region name from a TZ database and time zone offset from UTC. In BigQuery a time zone conversion should be manually performed to match Oracle's `TIMESTAMP WITH LOCAL TIME ZONE` feature.
`INTERVAL YEAR TO MONTH`	`STRING`	Interval values can be stored as `STRING` data type in BigQuery.
`INTERVAL DAY TO SECOND`	`STRING`	Interval values can be stored as `STRING` data type in BigQuery.
`RAW`	`BYTES`	`BYTES` data type can be used to store variable-length binary data. If this field is not queried and used in analytics, a better option is to store binary data on Cloud Storage.
`LONG RAW`	`BYTES`	`BYTES` data type can be used to store variable-length binary data. If this field is not queried and used in analytics, a better option is to store binary data on Cloud Storage.
`ROWID`	`STRING`	These data types are used Oracle internally to specify unique addresses to rows in a table. Generally, `ROWID` or `UROWID` field should not be used in applications. But if this is the case, `STRING` data type can be used to hold this data.

Type formatting

Oracle SQL uses a set of default formats set as parameters for displaying expressions and column data, and for conversions between data types. For example, NLS_DATE_FORMAT set as YYYY/MM/DD formats dates as YYYY/MM/DD by default. You can find more information about the NLS settings in the Oracle online documentation. In BigQuery, there are no initialization parameters.

By default, BigQuery expects all source data to be UTF-8 encoded when loading. Optionally, if you have CSV files with data encoded in ISO-8859-1 format, you can explicitly specify the encoding when you import your data so that BigQuery can properly convert your data to UTF-8 during the import process.

It is only possible to import data that is ISO-8859-1 or UTF-8 encoded. BigQuery stores and returns the data as UTF-8 encoded. Intended date format or time zone can be set in DATE and TIMESTAMP functions.

Timestamp and date type formatting

When you convert timestamp and date formatting elements from Oracle to BigQuery, you must pay attention to time zone differences between TIMESTAMP and DATETIME as summarized in the following table.

Notice there are no parentheses in the Oracle formats because the formats (CURRENT_*) are keywords, not functions.

Oracle	BigQuery	Notes
`CURRENT_TIMESTAMP`	`TIMESTAMP` information in Oracle can have different time zone information, which is defined using `WITH TIME ZONE` in column definition or setting `TIME_ZONE` variable.	If possible, use the `CURRENT_TIMESTAMP()` function, which is formatted in ISO format. However, the output format does always show the UTC time zone. (Internally, BigQuery does not have a time zone.) Note the following details on differences in the ISO format: `DATETIME` is formatted based on output channel conventions. In the BigQuery command-line tool and BigQuery console `DATETIME` is formatted using a `T` separator according to RFC 3339. However, in Python and Java JDBC, a space is used as a separator. If you want to use an explicit format, use the `FORMAT_DATETIME`() function, which makes an explicit cast a string. For example, the following expression always returns a space separator: `CAST(CURRENT_DATETIME() AS STRING)`
`CURRENT_DATE SYSDATE`	Oracle uses 2 types for date: type 12 type 13 Oracle uses type 12 when storing dates. Internally, these are numbers with fixed-length. Oracle uses type 13 when a is returned by `SYSDATE or CURRENT_DATE`	BigQuery has a separate `DATE` format that always returns a date in ISO 8601 format. `DATE_FROM_UNIX_DATE` can't be used because it is 1970-based.
`CURRENT_DATE-3`	Date values are represented as integers. Oracle supports arithmetic operators for date types.	For date types, use `DATE_ADD`() or `DATE_SUB`(). BigQuery uses arithmetic operators for data types: `INT64`, `NUMERIC`, and `FLOAT64`.
`NLS_DATE_FORMAT`	Set the session or system date format.	BigQuery always uses ISO 8601, so make sure you convert Oracle dates and times.

Query syntax

This section addresses differences in query syntax between Oracle and BigQuery.

`SELECT` statements

Most Oracle SELECT statements are compatible with BigQuery.

Functions, operators, and expressions

The following sections list mappings between Oracle functions and BigQuery equivalents.

Comparison operators

Oracle and BigQuery comparison operators are ANSI SQL:2011 compliant. The comparison operators in the table below are the same in both BigQuery and Oracle. You can use REGEXP_CONTAINS instead of REGEXP_LIKE in BigQuery.

Operator	Description
`"="`	Equal
`<>`	Not equal
`!=`	Not equal
`>`	Greater than
`>=`	Greater than or equal
`<`	Less than
`<=`	Less than or equal
`IN ( )`	Matches a value in a list
`NOT`	Negates a condition
`BETWEEN`	Within a range (inclusive)
`IS NULL`	`NULL` value
`IS NOT NULL`	Not `NULL` value
`LIKE`	Pattern matching with %
`EXISTS`	Condition is met if subquery returns at least one row

The operators on the table are the same both in BigQuery and Oracle.

Logical expressions and functions

Oracle	BigQuery
`CASE`	`CASE`
`COALESCE`	`COALESCE(expr1, ..., exprN)`
`DECODE`	`CASE.. WHEN.. END`
`NANVL`	`IFNULL`
`FETCH NEXT>`	`LIMIT`
`NULLIF`	`NULLIF(expression, expression_to_match)`
`NVL`	`IFNULL(expr, 0), COALESCE(exp, 0)`
`NVL2`	`IF(expr, true_result, else_result)`

Aggregate functions

The following table shows mappings between common Oracle aggregate, statistical aggregate, and approximate aggregate functions with their BigQuery equivalents:

Oracle	BigQuery
`ANY_VALUE` (from Oracle 19c)	`ANY_VALUE`
`APPROX_COUNT`	`HLL_COUNT set of functions with specified precision`
`APPROX_COUNT_DISTINCT`	`APPROX_COUNT_DISTINCT`
`APPROX_COUNT_DISTINCT_AGG`	`APPROX_COUNT_DISTINCT`
`APPROX_COUNT_DISTINCT_DETAIL`	`APPROX_COUNT_DISTINCT`
`APPROX_PERCENTILE(percentile) WITHIN GROUP (ORDER BY expression)`	`APPROX_QUANTILES(expression, 100)[ OFFSET(CAST(TRUNC(percentile * 100) as INT64))]` BigQuery doesn't support the rest of arguments that Oracle defines.
APPROX_PERCENTILE_AGG	`APPROX_QUANTILES(expression, 100)[ OFFSET(CAST(TRUNC(percentile * 100) as INT64))]`
`APPROX_PERCENTILE_DETAIL`	`APPROX_QUANTILES(expression, 100)[OFFSET(CAST(TRUNC(percentile * 100) as INT64))]`
`APPROX_SUM`	`APPROX_TOP_SUM(expression, weight, number)`
`AVG`	`AVG`
`BIT_COMPLEMENT`	bitwise not operator: ~
`BIT_OR`	`BIT_OR, X \| Y`
`BIT_XOR`	`BIT_XOR, X ^ Y`
`BITAND`	`BIT_AND, X & Y`
`CARDINALITY`	`COUNT`
`COLLECT`	BigQuery doesn't support `TYPE AS TABLE OF`. Consider using `STRING_AGG()` or `ARRAY_AGG()` in BigQuery
`CORR/CORR_K/` `CORR_S`	`CORR`
`COUNT`	`COUNT`
`COVAR_POP`	`COVAR_POP`
`COVAR_SAMP`	`COVAR_SAMP`
`FIRST`	Does not exist implicitly in BigQuery. Consider using user-defined functions (UDFs).
`GROUP_ID`	Not used in BigQuery
`GROUPING`	Not used in BigQuery
`GROUPING_ID`	Not used in BigQuery.
`LAST`	Does not exist implicitly in BigQuery. Consider using UDFs.
`LISTAGG`	`STRING_AGG, ARRAY_CONCAT_AGG(expression [ORDER BY key [{ASC\|DESC}] [, ... ]] [LIMIT n])`
`MAX`	`MAX`
`MIN`	`MIN`
`OLAP_CONDITION`	Oracle specific, does not exist in BigQuery.
`OLAP_EXPRESSION`	Oracle specific, does not exist in BigQuery.
`OLAP_EXPRESSION_BOOL`	Oracle specific, does not exist in BigQuery.
`OLAP_EXPRESSION_DATE`	Oracle specific, does not exist in BigQuery.
`OLAP_EXPRESSION_TEXT`	Oracle specific, does not exist in BigQuery.
`OLAP_TABLE`	Oracle specific, does not exist in BigQuery.
`POWERMULTISET`	Oracle specific, does not exist in BigQuery.
`POWERMULTISET_BY_CARDINALITY`	Oracle specific, does not exist in BigQuery.
`QUALIFY`	Oracle specific, does not exist in BigQuery.
`REGR_AVGX`	`AVG(` `IF(dep_var_expr is NULL` `OR ind_var_expr is NULL,` `NULL, ind_var_expr)` `)`
`REGR_AVGY`	`AVG(` `IF(dep_var_expr is NULL` `OR ind_var_expr is NULL,` `NULL, dep_var_expr)` `)`
`REGR_COUNT`	`SUM(` `IF(dep_var_expr is NULL` `OR ind_var_expr is NULL,` `NULL, 1)` `)`
`REGR_INTERCEPT`	`AVG(dep_var_expr) - AVG(ind_var_expr) * (COVAR_SAMP(ind_var_expr,dep_var_expr) / VARIANCE(ind_var_expr) )`
`REGR_R2`	`(COUNT(dep_var_expr) * SUM(ind_var_expr * dep_var_expr) - SUM(dep_var_expr) * SUM(ind_var_expr)) / SQRT( (COUNT(ind_var_expr) * SUM(POWER(ind_var_expr, 2)) * POWER(SUM(ind_var_expr),2)) * (COUNT(dep_var_expr) * SUM(POWER(dep_var_expr, 2)) * POWER(SUM(dep_var_expr), 2)))`
`REGR_SLOPE`	`COVAR_SAMP(ind_var_expr,` `dep_var_expr)` `/ VARIANCE(ind_var_expr)`
`REGR_SXX`	`SUM(POWER(ind_var_expr, 2)) - COUNT(ind_var_expr) * POWER(AVG(ind_var_expr),2)`
`REGR_SXY`	`SUM(ind_var_exprdep_var_expr) - COUNT(ind_var_expr) AVG(ind) * AVG(dep_var_expr)`
`REGR_SYY`	`SUM(POWER(dep_var_expr, 2)) - COUNT(dep_var_expr) * POWER(AVG(dep_var_expr),2)`
`ROLLUP`	`ROLLUP`
`STDDEV_POP`	`STDDEV_POP`
`STDDEV_SAMP`	`STDDEV_SAMP, STDDEV`
`SUM`	`SUM`
`VAR_POP`	`VAR_POP`
`VAR_SAMP`	`VAR_SAMP, VARIANCE`
`WM_CONCAT`	`STRING_AGG`

BigQuery offers the following additional aggregate functions:

Analytical functions

The following table shows mappings between common Oracle analytic and aggregate analytic functions with their BigQuery equivalents.

Oracle	BigQuery
`AVG`	`AVG`
`BIT_COMPLEMENT`	bitwise not operator: ~
`BIT_OR`	`BIT_OR, X \| Y`
`BIT_XOR`	`BIT_XOR, X ^ Y`
`BITAND`	`BIT_AND, X & Y`
`BOOL_TO_INT`	`CAST(X AS INT64)`
`COUNT`	`COUNT`
`COVAR_POP`	`COVAR_POP`
`COVAR_SAMP`	`COVAR_SAMP`
`CUBE_TABLE`	Isn't supported in BigQuery. Consider using a BI tool or a custom UDF
`CUME_DIST`	`CUME_DIST`
`DENSE_RANK(ANSI)`	`DENSE_RANK`
`FEATURE_COMPARE`	Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML
`FEATURE_DETAILS`	Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML
`FEATURE_ID`	Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML
`FEATURE_SET`	Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML
`FEATURE_VALUE`	Does not exist implicitly in BigQuery. Consider using UDFs and BigQuery ML
`FIRST_VALUE`	`FIRST_VALUE`
`HIER_CAPTION`	Hierarchical queries are not supported in BigQuery.
`HIER_CHILD_COUNT`	Hierarchical queries are not supported in BigQuery.
`HIER_COLUMN`	Hierarchical queries are not supported in BigQuery.
`HIER_DEPTH`	Hierarchical queries are not supported in BigQuery.
`HIER_DESCRIPTION`	Hierarchical queries are not supported in BigQuery.
`HIER_HAS_CHILDREN`	Hierarchical queries are not supported in BigQuery.
`HIER_LEVEL`	Hierarchical queries are not supported in BigQuery.
`HIER_MEMBER_NAME`	Hierarchical queries are not supported in BigQuery.
`HIER_ORDER`	Hierarchical queries are not supported in BigQuery.
`HIER_UNIQUE_MEMBER_NAME`	Hierarchical queries are not supported in BigQuery.
`LAST_VALUE`	`LAST_VALUE`
`LAG`	`LAG`
`LEAD`	`LEAD`
`LISTAGG`	`ARRAY_AGG STRING_AGG ARRAY_CONCAT_AGG`
`MATCH_NUMBER`	Pattern recognition and calculation can be done with regular expressions and UDFs in BigQuery
`MATCH_RECOGNIZE`	Pattern recognition and calculation can be done with regular expressions and UDFs in BigQuery
`MAX`	`MAX`
`MEDIAN`	`PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER()`
`MIN`	`MIN`
`NTH_VALUE`	`NTH_VALUE (value_expression, constant_integer_expression [{RESPECT \| IGNORE} NULLS])`
`NTILE`	`NTILE(constant_integer_expression)`
`PERCENT_RANK PERCENT_RANKM`	`PERCENT_RANK`
`PERCENTILE_CONT PERCENTILE_DISC`	`PERCENTILE_CONT`
`PERCENTILE_CONT PERCENTILE_DISC`	`PERCENTILE_DISC`
`PRESENTNNV`	Oracle specific, does not exist in BigQuery.
`PRESENTV`	Oracle specific, does not exist in BigQuery.
`PREVIOUS`	Oracle specific, does not exist in BigQuery.
`RANK`(ANSI)	`RANK`
`RATIO_TO_REPORT(expr) OVER (partition clause)`	`expr / SUM(expr) OVER (partition clause)`
`ROW_NUMBER`	`ROW_NUMBER`
`STDDEV_POP`	`STDDEV_POP`
`STDDEV_SAMP`	`STDDEV_SAMP, STDDEV`
`SUM`	`SUM`
`VAR_POP`	`VAR_POP`
`VAR_SAMP`	`VAR_SAMP, VARIANCE`
`VARIANCE`	`VARIANCE()`
`WIDTH_BUCKET`	UDF can be used.

Date/time functions

The following table shows mappings between common Oracle date/time functions and their BigQuery equivalents.

Oracle	BigQuery
`ADD_MONTHS(date, integer)`	`DATE_ADD(date, INTERVAL integer MONTH),` If date is a `TIMESTAMP` you can use `EXTRACT(DATE FROM TIMESTAMP_ADD(date, INTERVAL integer MONTH))`
`CURRENT_DATE`	`CURRENT_DATE`
`CURRENT_TIME`	`CURRENT_TIME`
`CURRENT_TIMESTAMP`	`CURRENT_TIMESTAMP`
`DATE - k`	`DATE_SUB(date_expression, INTERVAL k DAY)`
`DATE + k`	`DATE_ADD(date_expression, INTERVAL k DAY)`
`DBTIMEZONE`	BigQuery does not support the database time zone.
`EXTRACT`	`EXTRACT(DATE), EXTRACT(TIMESTAMP)`
`LAST_DAY`	`DATE_SUB( DATE_TRUNC( DATE_ADD( date_expression, INTERVAL 1 MONTH ), MONTH ), INTERVAL 1 DAY )`
`LOCALTIMESTAMP`	BigQuery doesn't support time zone settings.
`MONTHS_BETWEEN`	`DATE_DIFF(date_expression, date_expression, MONTH)`
`NEW_TIME`	`DATE(timestamp_expression, time zone) TIME(timestamp, time zone) DATETIME(timestamp_expression, time zone)`
`NEXT_DAY`	`DATE_ADD( DATE_TRUNC( date_expression, WEEK(day_value) ), INTERVAL 1 WEEK )`
`SYS_AT_TIME_ZONE`	`CURRENT_DATE([time_zone])`
`SYSDATE`	`CURRENT_DATE()`
`SYSTIMESTAMP`	`CURRENT_TIMESTAMP()`
`TO_DATE`	`PARSE_DATE`
`TO_TIMESTAMP`	`PARSE_TIMESTAMP`
`TO_TIMESTAMP_TZ`	`PARSE_TIMESTAMP`
`TZ_OFFSET`	Isn't supported in BigQuery. Consider using a custom UDF.
`WM_CONTAINS` `WM_EQUALS` `WM_GREATERTHAN` `WM_INTERSECTION` `WM_LDIFF` `WM_LESSTHAN` `WM_MEETS` `WM_OVERLAPS` `WM_RDIFF`	Periods are not used in BigQuery. UDFs can be used to compare two periods.

BigQuery offers the following additional date/time functions:

String functions

The following table shows mappings between Oracle string functions and their BigQuery equivalents:

Oracle	BigQuery
`ASCII`	`TO_CODE_POINTS(string_expr)[OFFSET(0)]`
`ASCIISTR`	BigQuery doesn't support UTF-16
`RAWTOHEX`	`TO_HEX`
`LENGTH`	`CHAR_LENGTH`
`LENGTH`	`CHARACTER_LENGTH`
`CHR`	`CODE_POINTS_TO_STRING( [mod(numeric_expr, 256)] )`
`COLLATION`	Doesn't exist in BigQuery. BigQuery doesn't support COLLATE in DML
`COMPOSE`	Custom user-defined function.
`CONCAT, (\|\| operator)`	`CONCAT`
`DECOMPOSE`	Custom user-defined function.
`ESCAPE_REFERENCE (UTL_I18N)`	Is not supported in BigQuery. Consider using a user-defined function.
`INITCAP`	Custom user-defined function.
`INSTR/INSTR2/INSTR4/INSTRB/INSTRC`	Custom user-defined function.
`LENGTH/LENGTH2/LENGTH4/LENGTHB/LENGTHC`	`LENGTH`
`LOWER`	`LOWER`
`LPAD`	`LPAD`
`LTRIM`	`LTRIM`
`NLS_INITCAP`	Custom user-defined function.
`NLS_LOWER`	`LOWER`
`NLS_UPPER`	`UPPER`
`NLSSORT`	Oracle specific, does not exist in BigQuery.
`POSITION`	`STRPOS(string, substring)`
`PRINTBLOBTOCLOB`	Oracle specific, does not exist in BigQuery.
`REGEXP_COUNT`	`ARRAY_LENGTH(REGEXP_EXTRACT_ALL(value, regex))`
`REGEXP_INSTR`	`STRPOS(source_string, REGEXP_EXTRACT(source_string, regexp_string))` Note: Returns first occurrence.
`REGEXP_REPLACE`	`REGEXP_REPLACE`
`REGEXP_LIKE`	`IF(REGEXP_CONTAINS,1,0)`
`REGEXP_SUBSTR`	`REGEXP_EXTRACT, REGEXP_EXTRACT_ALL`
`REPLACE`	`REPLACE`
`REVERSE`	`REVERSE`
`RIGHT`	`SUBSTR(source_string, -1, length)`
`RPAD`	`RPAD`
`RTRIM`	`RTRIM`
`SOUNDEX`	Isn't supported in BigQuery. Consider using a custom UDF
`STRTOK`	`SPLIT(instring, delimiter)[ORDINAL(tokennum)]` `Note: The entire delimiter string argument is used as a single delimiter. The default delimiter is a comma.`
`SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4`	`SUBSTR`
`TRANSLATE`	`REPLACE`
`TRANSLATE USING`	`REPLACE`
`TRIM`	`TRIM`
`UNISTR`	`CODE_POINTS_TO_STRING`
`UPPER`	`UPPER`
`\|\|` (VERTICAL BARS)	`CONCAT`

BigQuery offers the following additional string functions:

Math functions

The following table shows mappings between Oracle math functions and their BigQuery equivalents.

Oracle	BigQuery
`ABS`	`ABS`
`ACOS`	`ACOS`
`ACOSH`	`ACOSH`
`ASIN`	`ASIN`
`ASINH`	`ASINH`
`ATAN`	`ATAN`
`ATAN2`	`ATAN2`
`ATANH`	`ATANH`
`CEIL`	`CEIL`
`CEILING`	`CEILING`
`COS`	`COS`
`COSH`	`COSH`
`EXP`	`EXP`
`FLOOR`	`FLOOR`
`GREATEST`	`GREATEST`
`LEAST`	`LEAST`
`LN`	`LN`
`LNNVL`	use with `ISNULL`
`LOG`	`LOG`
`MOD (% operator)`	`MOD`
`POWER (** operator)`	`POWER, POW`
`DBMS_RANDOM.VALUE`	`RAND`
`RANDOMBYTES`	Isn't supported in BigQuery. Consider using a custom UDF and RAND function
`RANDOMINTEGER`	`CAST(FLOOR(10*RAND()) AS INT64)`
`RANDOMNUMBER`	Isn't supported in BigQuery. Consider using a custom UDF and RAND function
`REMAINDER`	`MOD`
`ROUND`	`ROUND`
`ROUND_TIES_TO_EVEN`	`ROUND()`
`SIGN`	`SIGN`
`SIN`	`SIN`
`SINH`	`SINH`
`SQRT`	`SQRT`
`STANDARD_HASH`	`FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512`
`STDDEV`	STDDEV
`TAN`	`TAN`
`TANH`	`TANH`
`TRUNC`	`TRUNC`
`NVL`	`IFNULL(expr, 0), COALESCE(exp, 0)`

BigQuery offers the following additional math functions:

Type conversion functions

The following table shows mappings between Oracle type conversion functions and their BigQuery equivalents.

Oracle	BigQuery
`BIN_TO_NUM`	`SAFE_CONVERT_BYTES_TO_STRING(value)` `CAST(x AS INT64)`
`BINARY2VARCHAR`	`SAFE_CONVERT_BYTES_TO_STRING(value)`
`CAST CAST_FROM_BINARY_DOUBLE CAST_FROM_BINARY_FLOAT CAST_FROM_BINARY_INTEGER CAST_FROM_NUMBER CAST_TO_BINARY_DOUBLE CAST_TO_BINARY_FLOAT CAST_TO_BINARY_INTEGER CAST_TO_NUMBER CAST_TO_NVARCHAR2 CAST_TO_RAW >CAST_TO_VARCHAR`	`CAST(expr AS typename)`
`CHARTOROWID`	Oracle specific not needed.
`CONVERT`	BigQuery doesn't support character sets. Consider using custom user-defined function.
`EMPTY_BLOB`	`BLOB` is not used in BigQuery.
`EMPTY_CLOB`	`CLOB` is not used in BigQuery.
`FROM_TZ`	Types with time zones are not supported in BigQuery. Consider using a user-defined function and FORMAT_TIMESTAMP
`INT_TO_BOOL`	`CAST`
`IS_BIT_SET`	Does not exist implicitly in BigQuery. Consider using UDFs
`NCHR`	UDF can be used to get char equivalent of binary
`NUMTODSINTERVAL`	`INTERVAL` data type is not supported in BigQuery
`NUMTOHEX`	Isn't supported in BigQuery. Consider using a custom UDF and `TO_HEX` function
`NUMTOHEX2`
`NUMTOYMINTERVAL`	`INTERVAL` data type is not supported in BigQuery.
`RAW_TO_CHAR`	Oracle specific, does not exist in BigQuery.
`RAW_TO_NCHAR`	Oracle specific, does not exist in BigQuery.
`RAW_TO_VARCHAR2`	Oracle specific, does not exist in BigQuery.
`RAWTOHEX`	Oracle specific, does not exist in BigQuery.
`RAWTONHEX`	Oracle specific, does not exist in BigQuery.
`RAWTONUM`	Oracle specific, does not exist in BigQuery.
`RAWTONUM2`	Oracle specific, does not exist in BigQuery.
`RAWTOREF`	Oracle specific, does not exist in BigQuery.
`REFTOHEX`	Oracle specific, does not exist in BigQuery.
`REFTORAW`	Oracle specific, does not exist in BigQuery.
`ROWIDTOCHAR`	`ROWID` is Oracle specific type and does not exist in BigQuery. This value should be represented as string.
`ROWIDTONCHAR`	`ROWID` is Oracle specific type and does not exist in BigQuery. This value should be represented as string.
`SCN_TO_TIMESTAMP`	`SCN` is Oracle specific type and does not exist in BigQuery. This value should be represented as timestamp.
`TO_ACLID TO_ANYLOB TO_APPROX_COUNT_DISTINCT TO_APPROX_PERCENTILE TO_BINARY_DOUBLE TO_BINARY_FLOAT TO_BLOB TO_CHAR TO_CLOB TO_DATE TO_DSINTERVAL TO_LOB TO_MULTI_BYTE TO_NCHAR TO_NCLOB TO_NUMBER TO_RAW TO_SINGLE_BYTE TO_TIME` TO_TIMESTAMP TO_TIMESTAMP_TZ TO_TIME_TZ TO_UTC_TIMEZONE_TZ TO_YMINTERVAL	`CAST(expr AS typename)` `PARSE_DATE` `PARSE_TIMESTAMP` Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type.
`TREAT`	Oracle specific, does not exist in BigQuery.
`VALIDATE_CONVERSION`	Isn't supported in BigQuery. Consider using a custom UDF
`VSIZE`	Isn't supported in BigQuery. Consider using a custom UDF

JSON functions

The following table shows mappings between Oracle JSON functions and their BigQuery equivalents.

Oracle	BigQuery
`AS_JSON`	`TO_JSON_STRING(value[, pretty_print])`
`JSON_ARRAY`	Consider using UDFs and `TO_JSON_STRING` function
`JSON_ARRAYAGG`	Consider using UDFs and `TO_JSON_STRING` function
`JSON_DATAGUIDE`	Custom user-defined function.
`JSON_EQUAL`	Custom user-defined function.
`JSON_EXIST`	Consider using UDFs and `JSON_EXTRACT` or `JSON_EXTRACT_SCALAR`
`JSON_MERGEPATCH`	Custom user-defined function.
`JSON_OBJECT`	Is not supported by BigQuery.
`JSON_OBJECTAGG`	Is not supported by BigQuery.
`JSON_QUERY`	Consider using UDFs and `JSON_EXTRACT` or `JSON_EXTRACT_SCALAR`.
`JSON_TABLE`	Custom user-defined function.
`JSON_TEXTCONTAINS`	Consider using UDFs and `JSON_EXTRACT` or `JSON_EXTRACT_SCALAR`.
`JSON_VALUE`	`JSON_EXTRACT_SCALAR`

XML functions

BigQuery does not provide implicit XML functions. XML can be loaded to BigQuery as string and UDFs can be used to parse XML. Alternatively, XML processing be done by an ETL/ELT tool such as Dataflow. The following list shows Oracle XML functions:

Oracle	BigQuery
`DELETEXML`	BigQuery UDFs or ETL tool like Dataflow can be used to process XML.
`ENCODE_SQL_XML`
`EXISTSNODE`
`EXTRACTCLOBXML`
`EXTRACTVALUE`
`INSERTCHILDXML`
`INSERTCHILDXMLAFTER`
`INSERTCHILDXMLBEFORE`
`INSERTXMLAFTER`
`INSERTXMLBEFORE`
`SYS_XMLAGG`
`SYS_XMLANALYZE`
`SYS_XMLCONTAINS`
`SYS_XMLCONV`
`SYS_XMLEXNSURI`
`SYS_XMLGEN`
`SYS_XMLI_LOC_ISNODE`
`SYS_XMLI_LOC_ISTEXT`
`SYS_XMLINSTR`
`SYS_XMLLOCATOR_GETSVAL`
`SYS_XMLNODEID`
`SYS_XMLNODEID_GETLOCATOR`
`SYS_XMLNODEID_GETOKEY`
`SYS_XMLNODEID_GETPATHID`
`SYS_XMLNODEID_GETPTRID`
`SYS_XMLNODEID_GETRID`
`SYS_XMLNODEID_GETSVAL`
`SYS_XMLT_2_SC`
`SYS_XMLTRANSLATE`
`SYS_XMLTYPE2SQL`
`UPDATEXML`
`XML2OBJECT`
`XMLCAST`
`XMLCDATA`
`XMLCOLLATVAL`
`XMLCOMMENT`
`XMLCONCAT`
`XMLDIFF`
`XMLELEMENT`
`XMLEXISTS`
`XMLEXISTS2`
`XMLFOREST`
`XMLISNODE`
`XMLISVALID`
`XMLPARSE`
`XMLPATCH`
`XMLPI`
`XMLQUERY`
`XMLQUERYVAL`
`XMLSERIALIZE`
`XMLTABLE`
`XMLTOJSON`
`XMLTRANSFORM`
`XMLTRANSFORMBLOB`
`XMLTYPE`

Machine learning functions

Machine learning (ML) functions in Oracle and BigQuery are different. Oracle requires advanced analytics pack and licenses to do ML on the database. Oracle uses the DBMS_DATA_MINING package for ML. Converting Oracle data miner jobs requires rewriting the code, you can choose from comprehensive Google AI product offerings such as BigQuery ML, AI APIs (including Speech-to-Text, Text-to-Speech , Dialogflow, Cloud Translation, NLP, Cloud Vision, and Timeseries Insights API, AutoML, AutoML Tables or AI Platform. Google user-managed notebooks can be used as a development environment for data scientists and Google AI Platform Training can be used to run training and scoring workloads at scale. The following table shows Oracle ML functions:

Oracle	BigQuery
`CLASSIFIER`	See BigQuery ML for machine learning classifier and regression options
`CLUSTER_DETAILS`
`CLUSTER_DISTANCE`
`CLUSTER_ID`
`CLUSTER_PROBABILITY`
`CLUSTER_SET`
`PREDICTION`
`PREDICTION_BOUNDS`
`PREDICTION_COST`
`PREDICTION_DETAILS`
`PREDICTION_PROBABILITY`
`PREDICTION_SET`

Security functions

The following table shows the functions for identifying the user in Oracle and BigQuery:

Oracle	BigQuery
`UID`	`SESSION_USER`
`USER/SESSION_USER/CURRENT_USER`	`SESSION_USER()`

Set or array functions

The following table shows set or array functions in Oracle and their equivalents in BigQuery:

Oracle	BigQuery
`MULTISET`	`ARRAY_AGG`
`MULTISET EXCEPT`	`ARRAY_AGG([DISTINCT] expression)`
`MULTISET INTERSECT`	`ARRAY_AGG([DISTINCT])`
`MULTISET UNION`	`ARRAY_AGG`

Window functions

The following table shows window functions in Oracle and their equivalents in BigQuery.

Oracle	BigQuery
`LAG`	`LAG (value_expression[, offset [, default_expression]])`
`LEAD`	`LEAD (value_expression[, offset [, default_expression]])`

Hierarchical or recursive queries

Hierarchical or recursive queries are not used in BigQuery. If the depth of the hierarchy is known similar functionality can be achieved with joins, as illustrated in the following example. Another solution would be to utilize the BigQueryStorage API and Spark.

select
  array(
    select e.update.element
    union all
    select c1 from e.update.element.child as c1
    union all
    select c2 from e.update.element.child as c1, c1.child as c2
    union all
    select c3 from e.update.element.child as c1, c1.child as c2, c2.child as c3
    union all
    select c4 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4
    union all
    select c5 from e.update.element.child as c1, c1.child as c2, c2.child as c3, c3.child as c4, c4.child as c5
  ) as flattened,
  e as event
from t, t.events as e

The following table shows hierarchical functions in Oracle.

Oracle	BigQuery
`DEPTH`	Hierarchical queries are not used in BigQuery.
`PATH`
`SYS_CONNECT_BY_PATH (hierarchical)`

UTL functions

UTL_File package is mainly used for reading and writing the operating system files from PL/SQL. Cloud Storage can be used for any kind of raw file staging. External tables and BigQuery load and export should be used to read and write files from and to Cloud Storage. For more information, see Introduction to external data sources.

Spatial functions

You can use BigQuery geospatial analytics to replace spatial functionality. There are SDO_* functions and types in Oracle such as SDO_GEOM_KEY, SDO_GEOM_MBR, SDO_GEOM_MMB. These functions are used for spatial analysis. You can use geospatial analytics to do spatial analysis.

DML syntax

This section addresses differences in data management language syntax between Oracle and BigQuery.

`INSERT` statement

Most Oracle INSERT statements are compatible with BigQuery. The following table shows exceptions.

DML scripts in BigQuery have slightly different consistency semantics than the equivalent statements in Oracle. For an overview of snapshot isolation and session and transaction handling, see the CREATE [UNIQUE] INDEX section elsewhere in this document.

Oracle BigQuery

Oracle	BigQuery
`INSERT INTO table VALUES (...);`	`INSERT INTO table (...) VALUES (...);` Oracle offers a `DEFAULT` keyword for non-nullable columns. Note: In BigQuery, omitting column names in the `INSERT` statement only works if values for all columns in the target table are included in ascending order based on their ordinal positions.
`INSERT INTO table VALUES (1,2,3); INSERT INTO table VALUES (4,5,6); INSERT INTO table VALUES (7,8,9); INSERT ALL INTO table (col1, col2) VALUES ('val1_1', 'val1_2') INTO table (col1, col2) VALUES ('val2_1', 'val2_2') INTO table (col1, col2) VALUES ('val3_1', 'val3_2') . . . SELECT 1 FROM DUAL;`	`INSERT INTO table VALUES (1,2,3), (4,5,6), (7,8,9);` BigQuery imposes DML quotas, which restrict the number of DML statements you can execute daily. To make the best use of your quota, consider the following approaches: Combine multiple rows in a single `INSERT` statement, instead of one row per `INSERT` operation. Combine multiple DML statements (including `INSERT`) using a `MERGE` statement. Use `CREATE TABLE ... AS SELECT` to create and populate new tables.

INSERT INTO table VALUES (...);

INSERT INTO table (...) VALUES (...);

Oracle offers a DEFAULT keyword for non-nullable columns.

Note: In BigQuery, omitting column names in the INSERT statement only works if values for all columns in the target table are included in ascending order based on their ordinal positions.

INSERT INTO table VALUES (1,2,3);

    INSERT INTO table VALUES (4,5,6);

    INSERT INTO table VALUES (7,8,9);

    INSERT ALL

    INTO table (col1, col2) VALUES ('val1_1', 'val1_2')

    INTO table (col1, col2) VALUES ('val2_1', 'val2_2')

    INTO table (col1, col2) VALUES ('val3_1', 'val3_2')

    .

    .

    .

    SELECT 1 FROM DUAL;

INSERT INTO table VALUES (1,2,3),
(4,5,6),
(7,8,9);

BigQuery imposes DML quotas, which restrict the number of DML statements you can execute daily. To make the best use of your quota, consider the following approaches:

Combine multiple rows in a single INSERT statement, instead of one row per INSERT operation.
Combine multiple DML statements (including INSERT) using a MERGE statement.
Use CREATE TABLE ... AS SELECT to create and populate new tables.

`UPDATE` statement

Oracle UPDATE statements are mostly compatible with BigQuery, however, in BigQuery the UPDATE statement must have a WHERE clause.

As a best practice, you should prefer batch DML statements over multiple single UPDATE and INSERT statements. DML scripts in BigQuery have slightly different consistency semantics than equivalent statements in Oracle. For an overview on snapshot isolation and session and transaction handling see the CREATE INDEX section in this document.

The following table shows Oracle UPD