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.

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

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_expr*dep_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:

• ANY_VALUE
• APPROX_TOP_COUNT
• COUNTIF
• LOGICAL_AND
• LOGICAL_OR

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:

• CURRENT_DATETIME
• DATE_FROM_UNIX_DATE
• DATE_TRUNC
• DATETIME
• DATETIME_ADD
• DATETIME_DIFF
• DATETIME_SUB
• DATETIME_TRUNC
• FORMAT_DATE
• FORMAT_DATETIME

• FORMAT_TIME

• FORMAT_TIMESTAMP

• PARSE_DATETIME

• PARSE_TIME

• STRING

• TIME

• TIME_ADD

• TIME_DIFF

• TIME_SUB

• TIME_TRUNC

• TIMESTAMP

• TIMESTAMP_ADD

• TIMESTAMP_DIFF

• TIMESTAMP_MICROS

• TIMESTAMP_MILLIS

• TIMESTAMP_SECONDS

• TIMESTAMP_SUB

• TIMESTAMP_TRUNC

• UNIX_DATE

• UNIX_MICROS

• UNIX_MILLIS

• UNIX_SECONDS

String functions

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

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

BigQuery offers the following additional string functions:

• BYTE_LENGTH
• CODE_POINTS_TO_BYTES
• ENDS_WITH
• FROM_BASE32
• FROM_BASE64
• FROM_HEX
• NORMALIZE
• NORMALIZE_AND_CASEFOLD
• REPEAT
• SAFE_CONVERT_BYTES_TO_STRING
• SPLIT
• STARTS_WITH
• STRPOS
• TO_BASE32
• TO_BASE64
• TO_CODE_POINTS

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:

• DIV
• IEEE_DIVIDE
• IS_INF
• IS_NAN
• LOG10
• SAFE_DIVIDE

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