Oracle SQL translation guide

This document details the similarities and differences in SQL syntax betweenOracle and BigQuery to help you plan your migration. Usebatch SQL translation tomigrate your SQL scripts in bulk, orinteractive SQL translationto translate ad-hoc queries.

Note: In some cases, there is no direct mapping between a SQL element in Oracleand BigQuery. However, in most cases, you can achieve the same functionality inBigQuery that you can in Oracle using an alternative means, as shown in theexamples in this document.

Data types

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

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 displayingexpressions and column data, and for conversions between data types. Forexample,NLS_DATE_FORMAT set asYYYY/MM/DD formats dates asYYYY/MM/DDby default. You can find more information about the NLS settings in the Oracleonline documentation.In BigQuery, there are no initialization parameters.

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

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

Timestamp and date type formatting

When you convert timestamp and date formatting elements from Oracle toBigQuery, you must pay attention to time zone differences betweenTIMESTAMP andDATETIME 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 inISO 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 usesISO 8601, so make sure you convert Oracle dates and times.

Query syntax

This section addresses differences in query syntax between Oracle andBigQuery.

`SELECT` statements

Most OracleSELECT statements are compatible with BigQuery.

Functions, operators, and expressions

The following sections list mappings between Oracle functions andBigQuery equivalents.

Comparison operators

Oracle and BigQuery comparison operators are ANSI SQL:2011compliant. The comparison operators in the table below are the same in bothBigQuery and Oracle. You can useREGEXP_CONTAINSinstead ofREGEXP_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 usinguser-defined functions (UDFs).
`GROUP_ID`	Not used in BigQuery
`GROUPING`	`GROUPING`
`GROUPING_ID`	Not used in BigQuery.
`LAST`	Does not exist implicitly in BigQuery. Consider usingUDFs.
`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 theirBigQuery 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 theirBigQuery 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 beloaded to BigQuery as string and UDFs can be used to parse XML.Alternatively, XML processing be done by an ETL/ELT tool such asDataflow. The following list shows Oracle XMLfunctions:

Oracle	BigQuery
`DELETEXML`	BigQueryUDFs 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 aredifferent.Oracle requires advanced analytics pack and licenses to do ML on the database.Oracle uses theDBMS_DATA_MINING package for ML. Converting Oracle data minerjobs requires rewriting the code, you can choose from comprehensiveGoogle AIproduct offerings such asBigQuery ML,AI APIs (includingSpeech-to-Text,Text-to-Speech,Dialogflow,Cloud Translation,NLP,Cloud Vision, andTimeseries Insights API,AutoML,AutoML Tables orAI Platform. Googleuser-managed notebooks can be used as a developmentenvironment for data scientists and GoogleAI Platform Trainingcan be used to run training and scoring workloads at scale. The following tableshows Oracle ML functions:

Oracle	BigQuery
`CLASSIFIER`	SeeBigQuery 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 andBigQuery:

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 equivalentsin 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

Hierarchicalor recursive queries are not used in BigQuery. If the depth ofthe hierarchy is known similar functionality can be achieved with joins, asillustrated in the following example. Another solution would be to utilize theBigQueryStorage API andSpark.

selectarray(selecte.update.elementunionallselectc1frome.update.element.childasc1unionallselectc2frome.update.element.childasc1,c1.childasc2unionallselectc3frome.update.element.childasc1,c1.childasc2,c2.childasc3unionallselectc4frome.update.element.childasc1,c1.childasc2,c2.childasc3,c3.childasc4unionallselectc5frome.update.element.childasc1,c1.childasc2,c2.childasc3,c3.childasc4,c4.childasc5)asflattened,easeventfromt,t.eventsase

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_Filepackage is mainly used for reading and writing the operating system files fromPL/SQL. Cloud Storage can be used for any kind of raw file staging.External tables and BigQueryload andexportshould be used to read and write files from and to Cloud Storage. Formore information, seeIntroduction to external data sources.

Spatial functions

You can use BigQuery geospatial analytics to replace spatialfunctionality. ThereareSDO_* functions and types in Oracle such asSDO_GEOM_KEY,SDO_GEOM_MBR,SDO_GEOM_MMB. These functions are used for spatialanalysis. You can usegeospatial analyticsto do spatial analysis.

DML syntax

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

`INSERT` statement

Most OracleINSERT statements are compatible with BigQuery. Thefollowing table shows exceptions.

DML scripts in BigQuery have slightly different consistencysemantics than the equivalent statements in Oracle. For an overview of snapshotisolation and session and transaction handling, see theCREATE [UNIQUE] INDEXsection elsewhere in this document.

Oracle BigQuery

Oracle	BigQuery
`INSERT INTOtable VALUES (...);`	`INSERT INTOtable (...) 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 INTOtable VALUES (1,2,3); INSERT INTOtable VALUES (4,5,6); INSERT INTOtable VALUES (7,8,9); INSERT ALL INTOtable (col1, col2) VALUES ('val1_1', 'val1_2') INTOtable (col1, col2) VALUES ('val2_1', 'val2_2') INTOtable (col1, col2) VALUES ('val3_1', 'val3_2') . . . SELECT 1 FROM DUAL;`	`INSERT INTOtable VALUES (1,2,3),(4,5,6), (7,8,9);` BigQuery imposesDML 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 INTOtable VALUES (...);

INSERT INTOtable (...) VALUES (...);

Oracle offers aDEFAULT keyword for non-nullable columns.

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

INSERT INTOtable VALUES (1,2,3);
    INSERT INTOtable VALUES (4,5,6);
    INSERT INTOtable VALUES (7,8,9);
    INSERT ALL
    INTOtable (col1, col2) VALUES ('val1_1', 'val1_2')
    INTOtable (col1, col2) VALUES ('val2_1', 'val2_2')
    INTOtable (col1, col2) VALUES ('val3_1', 'val3_2')
    .
    .
    .
    SELECT 1 FROM DUAL;

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

BigQuery imposesDML 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 singleINSERT statement, instead of one row perINSERT operation.
Combine multiple DML statements (includingINSERT) using aMERGE statement.
UseCREATE TABLE ... AS SELECT to create and populate new tables.

`UPDATE` statement

OracleUPDATE statements are mostly compatible with BigQuery,however, in BigQuery theUPDATE statement must have aWHEREclause.

As a best practice, you should prefer batch DML statements over multiple singleUPDATE andINSERT statements. DML scripts in BigQuery haveslightly different consistency semantics than equivalent statements in Oracle.For an overview on snapshot isolation and session and transaction handling seetheCREATE INDEX section in this document.

The following table shows OracleUPDATE statements and BigQuerystatements that accomplish the same tasks.

In BigQuery theUPDATE statement must have aWHERE clause.For more information aboutUPDATE in BigQuery, see theBigQuery UPDATE examplesin the DML documentation.

`DELETE` and`TRUNCATE` statements

TheDELETE andTRUNCATE statements are both ways to remove rows from a tablewithout affecting the table schema.TRUNCATE is not used in BigQuery.However, you can useDELETE statements to achieve the same effect.

In BigQuery, theDELETE statement must have aWHERE clause.For more information aboutDELETE in BigQuery, see theBigQueryDELETE examplesin the DML documentation.

Oracle	BigQuery
`DELETEdatabase.table;`	`DELETE FROMtable WHERE TRUE;`

`MERGE` statement

TheMERGE statement can combineINSERT,UPDATE, andDELETE operationsinto a singleUPSERT statement and perform the operations atomically. TheMERGE 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 differentconsistency semantics than the equivalent statements in Oracle.

DDL syntax

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

`CREATE TABLE` statement

Most OracleCREATE TABLEstatements are compatible with BigQuery, except for the followingconstraints and syntax elements, which are not used in BigQuery:

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

For more information aboutCREATE TABLE in BigQuery,see theBigQueryCREATE TABLEexamples.

Column options and attributes

Identity columns are introduced with Oracle 12c version which enablesauto-increment on a column. This is not used in BigQuery, thiscan be achieved with the following batch way. For more information about surrogatekeys 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 usesComment syntax to add comments on columns. This feature can besimilarly implemented in BigQuery using the column description asshown in the following table:

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

Temporary tables

Oracle supportstemporary 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 canprefix all the table names in this dataset withtemp to clearly denote thatthe tables are temporary.

Table TTL: Create a table that has a table-specific short time to liveusing 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 sameblock, use a temporary result using aWITHstatement or subquery.

`CREATE SEQUENCE` statement

Sequences are not used in BigQuery, this can be achieved with thefollowing batch way. For more information about surrogate keys and slowlychanging 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 BigQueryfor theCREATE VIEW statement.

Oracle	BigQuery	Notes
`CREATE VIEWview_name AS SELECT ...`	`CREATE VIEW view_name AS SELECT ...`
`CREATE OR REPLACE VIEWview_name AS SELECT ...`	`CREATE OR REPLACE VIEWview_name ASSELECT ...`
Not supported	`CREATE VIEW IF NOT EXISTSview_nameOPTIONS(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 doneautomatically. There is no need to specify refresh options (for example, oncommit or on schedule) in BigQuery. For more information, seeIntroduction to materialized views.

In case the base table keeps changing by appends only, the query that usesmaterialized view (whether view is explicitly referenced or selected by thequery optimizer) scans all materialized view plus a delta in the base tablesince 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 lastview refresh, the materialized view are not be scanned and hence query don'tget any savings until the next view refresh. Basically, any update or deletionin the base table invalidates the materialized view state.

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

The following table shows equivalents between Oracle and BigQueryfor theCREATE MATERIALIZED VIEW statement.

Oracle	BigQuery	Notes
`CREATE MATERIALIZED VIEWview_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 createfunctionality similar to indexes in Oracle.

Indexing for performance

BigQuery doesn't need explicit indexes, because it's acolumn-oriented database with query and storage optimization.BigQuery provides functionality such aspartitioning andclustering aswell asnested fields, which can increasequery 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 atable. If a process tries to insert or update data that has a value that'salready in the index the operation fails with an index violation.

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

To generate an error for duplicate records in BigQuery you canuse aMERGE statement from the staging table, as shown in the followingexample:

Oracle		BigQuery
`CREATE [UNIQUE] INDEXname;`		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 toremove duplicates independently in order to find errors in downstream systems.

BigQuery does not supportDEFAULT andIDENTITY (sequences) columns.

Locking

BigQuery doesn't have a lock mechanism like Oracle and can runconcurrent queries (up to yourquota). Only DML statementshave certainconcurrency limitsand might require atable lock during executionin some scenarios.

Procedural SQL statements

This section describes how to convert procedural SQL statements used in storedprocedures, 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 proceduralstatements to BigQuery scripting statements that have similarfunctionality.

`CREATE TRIGGER` statement

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

Variable declaration and assignment

The following table shows OracleDECLAREstatements and theirBigQuery 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`
`SETvar =value;`	`SETvar =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 BigQueryequivalent:

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 TABLEtable_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, seeConsistency guarantees and transaction isolation in this document.
`EXPLAIN PLAN ...`	Not used in BigQuery. Similar features are thequery plan explanation in the BigQuery web UI and the slot allocation, and inaudit logging in Stackdriver.
`SELECT * FROM DBA_[*];` (Oracle DBA_/ALL_/V$ views)	`SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES;` For more information, seeIntroduction to BigQuery INFORMATION_SCHEMA.
`SELECT * FROMGV$SESSION;` `SELECT * FROMV$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, seeView job details.
`START TRANSACTION;` `LOCK TABLEtable_A IN EXCLUSIVE MODE NOWAIT;` `DELETE FROMtable_A;` `INSERT INTOtable_A SELECT * FROMtable_B;` `COMMIT;`	Replacing the contents of a table with query output is the equivalent of a transaction. You can do this with either aquery or acopy operation. Using a query: `bq query --replace --destination_tabletable_A 'SELECT * FROMtable_B';` Using a copy: `bq cp -ftable_Atable_B`

Multi-statement and multi-line SQL statements

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

Error codes and messages

Oracle error codesandBigQuery error codes aredifferent. If your application logic is currently catching the errors, try toeliminate the source of the error, because BigQuery doesn'treturn the same error codes.

Consistency guarantees and transaction isolation

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

Transactions

Oracle provides read committed or serializabletransaction isolationlevels.Deadlocks are possible. Oracle insert append jobs run independently.

BigQuery alsosupports transactions.BigQuery helps ensureoptimistic concurrency control (first to commit wins) withsnapshot isolation,in which a query reads the last committed data before the query starts. Thisapproach guarantees the same level of consistency on a per-row, per-mutationbasis and across rows within the same DML statement, yet avoids deadlocks. Inthe case of multipleUPDATE statements against the same table, BigQueryswitches topessimistic concurrency control andqueues multipleUPDATE statements, automatically retrying in case of conflicts.INSERT DML statements andload jobs can run concurrently and independently to append to tables.

Rollback

Oracle supportsrollbacks. As there is no explicit transaction boundary in BigQuery, thereis no concept of an explicit rollback in BigQuery. Theworkarounds aretable decorators orusingFOR SYSTEM_TIME AS OF.

Database limits

CheckBigQuery latest quotas and limits. Manyquotas for large-volume users can be raised by contacting the Cloud Customer Care.The following table shows a comparison of the Oracle and BigQuerydatabase 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 withslot 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 includesdata type limits,physical database limits,logical database limits andprocess and runtime limits.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.

Movatterモバイル変換