Console
Start free

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: 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 GROUPING 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:

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 to work with BigQuery features. You can choose from comprehensive Google AI offerings, including the following products and features:

You can use BigQuery ML or Agent Platform development tools to develop, train, and evaluate ML models.

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

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:

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:

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

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:

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:

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.