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`	`INITCAP`
`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 UPDATE statements and BigQuery statements that accomplish the same tasks.

In BigQuery the UPDATE statement must have a WHERE clause. For more information about UPDATE in BigQuery, see the BigQuery UPDATE examples in the DML documentation.

`DELETE` and `TRUNCATE` statements

The DELETE and TRUNCATE statements are both ways to remove rows from a table without affecting the table schema. TRUNCATE is not used in BigQuery. However, you can use DELETE statements to achieve the same effect.

In BigQuery, the DELETE statement must have a WHERE clause. For more information about DELETE in BigQuery, see the BigQuery DELETE examples in the DML documentation.

Oracle	BigQuery
`DELETE database.table;`	`DELETE FROM table WHERE TRUE;`

`MERGE` statement

The MERGE statement can combine INSERT, UPDATE, and DELETE operations into a single UPSERT statement and perform the operations atomically. The MERGE operation must match, at most, one source row for each target row. BigQuery and Oracle both follow ANSI Syntax.

However, DML scripts in BigQuery have slightly different consistency semantics than the equivalent statements in Oracle.

DDL syntax

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

`CREATE TABLE` statement

Most Oracle CREATE TABLE statements are compatible with BigQuery, except for the following constraints and syntax elements, which are not used in BigQuery:

STORAGE
TABLESPACE
DEFAULT
GENERATED ALWAYS AS
ENCRYPT
PRIMARY KEY (col, ...). For more information, see CREATE INDEX.
UNIQUE INDEX. For more information, see CREATE INDEX.
CONSTRAINT..REFERENCES
DEFAULT
PARALLEL
COMPRESS

For more information about CREATE TABLE in BigQuery, see the BigQuery CREATE TABLE examples.

Column options and attributes

Identity columns are introduced with Oracle 12c version which enables auto-increment on a column. This is not used in BigQuery, this can be achieved with the following batch way. For more information about surrogate keys and slowly changing dimensions (SCD), refer to the following guides:

Oracle	BigQuery
`CREATE TABLE table ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );`	`INSERT INTO dataset.table SELECT *, ROW_NUMBER() OVER () AS id FROM dataset.table`

Column comments

Oracle uses Comment syntax to add comments on columns. This feature can be similarly implemented in BigQuery using the column description as shown in the following table:

Oracle	BigQuery
`Comment on column table is 'column desc';`	`CREATE TABLE dataset.table ( col1 STRING OPTIONS(description="column desc") );`

Temporary tables

Oracle supports temporary tables, which are often used to store intermediate results in scripts. Temporary tables are supported in BigQuery.

Oracle	BigQuery
`CREATE GLOBAL TEMPORARY TABLE temp_tab (x INTEGER, y VARCHAR2(50)) ON COMMIT DELETE ROWS; COMMIT;`	`CREATE TEMP TABLE temp_tab ( x INT64, y STRING ); DELETE FROM temp_tab WHERE TRUE;`

The following Oracle elements are not used in BigQuery:

ON COMMIT DELETE ROWS;
ON COMMIT PRESERVE ROWS;

There are also some other ways to emulate temporary tables in BigQuery:

Dataset TTL: Create a dataset that has a short time to live (for example, one hour) so that any tables created in the dataset are effectively temporary (since they won't persist longer than the dataset's time to live). You can prefix all the table names in this dataset with temp to clearly denote that the tables are temporary.

Table TTL: Create a table that has a table-specific short time to live using DDL statements similar to the following:

CREATE TABLE temp.name (col1, col2, ...)

OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR));

WITH clause: If a temporary table is needed only within the same block, use a temporary result using a WITH statement or subquery.

`CREATE SEQUENCE` statement

Sequences are not used in BigQuery, this can be achieved with the following batch way. For more information about surrogate keys and slowly changing dimensions (SCD), refer to the following guides:

INSERT INTO dataset.table
    SELECT *,
      ROW_NUMBER() OVER () AS id
      FROM dataset.table

`CREATE VIEW` statement

The following table shows equivalents between Oracle and BigQuery for the CREATE VIEW statement.

Oracle	BigQuery	Notes
`CREATE VIEW view_name AS SELECT ...`	`CREATE VIEW view_name AS SELECT ...`
`CREATE OR REPLACE VIEW view_name AS SELECT ...`	`CREATE OR REPLACE VIEW` `view_name AS` `SELECT ...`
Not supported	`CREATE VIEW IF NOT EXISTS` `view_name` `OPTIONS(view_option_list)` `AS SELECT ...`	Creates a new view only if the view does not currently exist in the specified dataset.

`CREATE MATERIALIZED VIEW` statement

In BigQuery materialized view refresh operations are done automatically. There is no need to specify refresh options (for example, on commit or on schedule) in BigQuery. For more information, see Introduction to materialized views.

In case the base table keeps changing by appends only, the query that uses materialized view (whether view is explicitly referenced or selected by the query optimizer) scans all materialized view plus a delta in the base table since the last view refresh. This means queries are faster and cheaper.

On the contrary, if there were any updates (DML UPDATE / MERGE) or deletions (DML DELETE, truncation, partition expiration) in the base table since the last view refresh, the materialized view are not be scanned and hence query don't get any savings until the next view refresh. Basically, any update or deletion in the base table invalidates the materialized view state.

Also, the data from the streaming buffer of the base table is not saved into materialized view. Streaming buffer is still being scanned fully regardless of whether materialized view is used.

The following table shows equivalents between Oracle and BigQuery for the CREATE MATERIALIZED VIEW statement.

Oracle	BigQuery	Notes
`CREATE MATERIALIZED VIEW view_name REFRESH FAST NEXT sysdate + 7 AS SELECT … FROM TABLE_1`	`CREATE MATERIALIZED VIEW view_name AS SELECT ...`

`CREATE [UNIQUE] INDEX` statement

This section describes approaches in BigQuery for how to create functionality similar to indexes in Oracle.

Indexing for performance

BigQuery doesn't need explicit indexes, because it's a column-oriented database with query and storage optimization. BigQuery provides functionality such as partitioning and clustering as well as nested fields, which can increase query efficiency and performance by optimizing how data is stored.

Indexing for consistency (UNIQUE, PRIMARY INDEX)

In Oracle, a unique index can be used to prevent rows with non-unique keys in a table. If a process tries to insert or update data that has a value that's already in the index the operation fails with an index violation.

Because BigQuery doesn't provide explicit indexes, a MERGE statement can be used instead to insert only unique records into a target table from a staging table while discarding duplicate records. However, there is no way to prevent a user with edit permissions from inserting a duplicate record.

To generate an error for duplicate records in BigQuery you can use a MERGE statement from the staging table, as shown in the following example:

Oracle		BigQuery
`CREATE [UNIQUE] INDEX name;`		MERGE `prototype.FIN_MERGE` t \ USING `prototype.FIN_TEMP_IMPORT` m \ ON t.col1 = m.col1 \ AND t.col2 = m.col2 \ WHEN MATCHED THEN \ UPDATE SET t.col1 = ERROR(CONCAT('Encountered Error for ', m.col1, ' ', m.col2)) \ WHEN NOT MATCHED THEN \ INSERT (col1,col2,col3,col4,col5,col6,col7,col8) VALUES(col1,col2,col3,col4,col5,col6, CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

More often, users prefer to remove duplicates independently in order to find errors in downstream systems.

BigQuery does not support DEFAULT and IDENTITY (sequences) columns.

Locking

BigQuery doesn't have a lock mechanism like Oracle and can run concurrent queries (up to your quota). Only DML statements have certain concurrency limits and might require a table lock during execution in some scenarios.

Procedural SQL statements

This section describes how to convert procedural SQL statements used in stored procedures, functions and triggers from Oracle to BigQuery.

`CREATE PROCEDURE` statement

Stored Procedure is supported as part of BigQuery Scripting Beta.

Oracle	BigQuery	Notes
`CREATE PROCEDURE`	`CREATE PROCEDURE`	Similar to Oracle, BigQuery supports `IN, OUT, INOUT` argument modes. Other syntax specifications are not supported in BigQuery.
`CREATE OR REPLACE PROCEDURE`	`CREATE OR REPLACE PROCEDURE`
`CALL`	`CALL`

The sections that follow describe ways to convert existing Oracle procedural statements to BigQuery scripting statements that have similar functionality.

`CREATE TRIGGER` statement

Triggers are not used in BigQuery. Row based application logic should be handled on the application layer. Trigger functionality can be achieved utilising the ingestion tool, Pub/Sub and/or Cloud Run functions during the ingestion time or utilising regular scans.

Variable declaration and assignment

The following table shows Oracle DECLAREstatements and their BigQuery equivalents.

Oracle	BigQuery
`DECLARE L_VAR NUMBER; BEGIN L_VAR := 10 + 20; END;`	`DECLARE L_VAR int64; BEGIN SET L_VAR = 10 + 20; SELECT L_VAR; END`
`SET var = value;`	`SET var = value;`

Cursor declarations and operations

BigQuery does not support cursors, so the following statements are not used in BigQuery:

DECLARE cursor_name CURSOR [FOR | WITH] ...
OPEN CUR_VAR FOR sql_str;
OPEN cursor_name [USING var, ...];
FETCH cursor_name INTO var, ...;
CLOSE cursor_name;

Dynamic SQL statements

The following Oracle Dynamic SQL statement and its BigQuery equivalent:

Oracle	BigQuery
`EXECUTE IMMEDIATE sql_str [USING IN OUT [, ...]];`	`EXECUTE IMMEDIATE sql_expression [INTO variable[, ...]] [USING identifier[, ...]]; ;`

Flow-of-control statements

The following table shows Oracle flow-of-control statements and their BigQuery equivalents.

Oracle	BigQuery
`IF condition THEN [if_statement_list] [ELSE else_statement_list ] END IF;`	`IF condition THEN [if_statement_list] [ELSE else_statement_list ] END IF;`
`SET SERVEROUTPUT ON; DECLARE x INTEGER DEFAULT 0; y INTEGER DEFAULT 0; BEGIN LOOP IF x>= 10 THEN EXIT; ELSIF x>= 5 THEN y := 5; END IF; x := x + 1; END LOOP; dbms_output.put_line(x\|\|','\|\|y); END; /`	`DECLARE x INT64 DEFAULT 0; DECLARE y INT64 DEFAULT 0; LOOP IF x>= 10 THEN LEAVE; ELSE IF x>= 5 THEN SET y = 5; END IF; END IF; SET x = x + 1; END LOOP; SELECT x,y;`
`LOOP sql_statement_list END LOOP;`	`LOOP sql_statement_list END LOOP;`
`WHILE boolean_expression DO sql_statement_list END WHILE;`	`WHILE boolean_expression DO sql_statement_list END WHILE;`
`FOR LOOP`	`FOR LOOP` is not used in BigQuery. Use other `LOOP` statements.
`BREAK`	`BREAK`
`CONTINUE`	`CONTINUE`
`CONTINUE/EXIT WHEN`	Use `CONTINUE` with `IF` condition.
`GOTO`	`GOTO` statement does not exist in BigQuery. Use `IF` condition.

Metadata and transaction SQL statements

Oracle	BigQuery
`GATHER_STATS_JOB`	Not used in BigQuery yet.
`LOCK TABLE table_name IN [SHARE/EXCLUSIVE] MODE NOWAIT;`	Not used in BigQuery yet.
`Alter session set isolation_level=serializable; /` `SET TRANSACTION ...`	BigQuery always uses Snapshot Isolation. For details, see Consistency guarantees and transaction isolation in this document.
`EXPLAIN PLAN ...`	Not used in BigQuery. Similar features are the query plan explanation in the BigQuery web UI and the slot allocation, and in audit logging in Stackdriver.
`SELECT * FROM DBA_[*];` (Oracle DBA_/ALL_/V$ views)	`SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES;` For more information, see Introduction to BigQuery INFORMATION_SCHEMA.
`SELECT * FROM GV$SESSION;` `SELECT * FROM V$ACTIVE_SESSION_HISTORY;`	BigQuery does not have the traditional session concept. You can view query jobs in the UI or export stackdriver audit logs to BigQuery and analyze BigQuery logs for analyzing jobs. For more information, see View job details.
`START TRANSACTION;` `LOCK TABLE table_A IN EXCLUSIVE MODE NOWAIT;` `DELETE FROM table_A;` `INSERT INTO table_A SELECT * FROM table_B;` `COMMIT;`	Replacing the contents of a table with query output is the equivalent of a transaction. You can do this with either a query or a copy operation. Using a query: `bq query --replace --destination_table table_A 'SELECT * FROM table_B';` Using a copy: `bq cp -f table_A table_B`

Multi-statement and multi-line SQL statements

Both Oracle and BigQuery support transactions (sessions) and therefore support statements separated by semicolons that are consistently executed together. For more information, see Multi-statement transactions.

Error codes and messages

Oracle error codes and BigQuery error codes are different. If your application logic is currently catching the errors, try to eliminate the source of the error, because BigQuery doesn't return the same error codes.

Consistency guarantees and transaction isolation

Both Oracle and BigQuery are atomic—that is, ACID-compliant on a per-mutation level across many rows. For example, a MERGE operation is atomic, even with multiple inserted and updated values.

Transactions

Oracle provides read committed or serializable transaction isolation levels. Deadlocks are possible. Oracle insert append jobs run independently.

BigQuery also supports transactions. BigQuery helps ensure optimistic concurrency control (first to commit wins) with snapshot isolation, in which a query reads the last committed data before the query starts. This approach guarantees the same level of consistency on a per-row, per-mutation basis and across rows within the same DML statement, yet avoids deadlocks. In the case of multiple UPDATE statements against the same table, BigQuery switches to pessimistic concurrency control and queues multiple UPDATE statements, automatically retrying in case of conflicts. INSERT DML statements and load jobs can run concurrently and independently to append to tables.

Rollback

Oracle supports rollbacks. As there is no explicit transaction boundary in BigQuery, there is no concept of an explicit rollback in BigQuery. The workarounds are table decorators or using FOR SYSTEM_TIME AS OF.

Database limits

Check BigQuery latest quotas and limits. Many quotas for large-volume users can be raised by contacting the Cloud Customer Care. The following table shows a comparison of the Oracle and BigQuery database limits.

Limit	Oracle	BigQuery
Tables per database	Unrestricted	Unrestricted
Columns per table	1000	10,000
Maximum row size	Unlimited (Depends on the column type)	100 MB
Column and table name length	If v12.2>= 128 Bytes Else 30 Bytes	16,384 Unicode characters
Rows per table	Unlimited	Unlimited
Maximum SQL request length	Unlimited	1 MB (maximum unresolved GoogleSQL query length) 12 MB (maximum resolved legacy and GoogleSQL query length) Streaming: 10 MB (HTTP request size limit) 10,000 (maximum rows per request)
Maximum request & response size	Unlimited	10 MB (request) and 10 GB (response), or virtually unlimited if you use pagination or the Cloud Storage API.
Maximum number of concurrent sessions	Limited by the sessions or processes parameters	100 concurrent queries (can be raised with slot reservation), 300 concurrent API requests per user.
Maximum number of concurrent (fast) loads	Limited by the sessions or processes parameters	No concurrency limit; jobs are queued. 100,000 load jobs per project per day.

Other Oracle Database limits includes data type limits, physical database limits, logical database limits and process and runtime limits.