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.

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.

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.

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

Logical expressions and functions

Aggregate functions

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

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.

Date/time functions

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

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:

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.

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.

JSON functions

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

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:

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:

Security functions

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

Set or array functions

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

Window functions

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

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.

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.

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.

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.

MERGE statement

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

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

DDL syntax

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

CREATE TABLE statement

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

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

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

Column options and attributes

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

• BigQuery Surrogate Keys
• BigQuery and surrogate keys: A practical approach

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:

Temporary tables

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

The following Oracle elements are not used in BigQuery:

• ON COMMIT DELETE ROWS;
• ON COMMIT PRESERVE ROWS;

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

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

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

  CREATE TABLE temp.name (col1, col2, ...)
  OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)); 

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

CREATE SEQUENCE statement

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

• BigQuery Surrogate Keys
• BigQuery and surrogate keys: A practical approach

 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.

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.

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:

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.

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.

Cursor declarations and operations

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

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

Dynamic SQL statements

The following Oracle Dynamic SQL statement and its BigQuery equivalent:

Flow-of-control statements

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

Metadata and transaction SQL statements

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.

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