Oracle to Snowflake migration: Migrating SQL code


Oracle to Snowflake Migration Series, Part 3

Since Snowflake Cloud Data Platform uses ANSI-compliant SQL, most of your existing SQL code on Oracle will execute in Snowflake. However, due to the numerous Oracle-specific SQL extensions, you will most likely have to make adjustments when migrating SQL code from Oracle to Snowflake.

Here are examples of some of the changes you may need to make to your Oracle SQL queries and code to have them run correctly in Snowflake. Please note that this is not an exhaustive list.

Migrating functions, procedures and packages

Snowflake supports functions and procedures, but not packages. Any code that was written in Oracle PL/SQL will not execute in Snowflake and will have to be rewritten.

SQL functions that use ANSI SQL syntax can be readily migrated from Oracle to Snowflake. However, if any Oracle specific SQL or PL/SQL code was used in functions, these would then have to be rewritten in Snowflake using JavaScript. Similarly as in Oracle, a function in Snowflake can return a scalar or a tabular result.

Stored procedures that use Oracle PL/SQL will have to be rewritten in Snowflake. Snowflake stored procedures use JavaScript for the control structures such as branching and looping, combined with SQL statements that are executed from the JavaScript API. Similarly as in Oracle, stored procedures can be granted rights to be executed by users even when the users are not granted rights to the underlying objects that are accessed by the procedures.

There is no concept of packages in Snowflake. When you migrate procedures and functions that are part of Oracle packages, you must create them as standalone procedures and functions.

Constraints and ROWID

Although Snowflake SQL syntax supports creating constraints such as primary key and foreign key, they are used only for compatibility and are not physically enforced. Whenever you have any SQL code that relies on constraints, they will not fail in Snowflake so any such logic will have to be revisited and redesigned.

In addition to the primary key, another feature in Oracle is the ROWID pseudocolumn, which returns the address of the row and can also be used to uniquely identify a record. Snowflake does not have a corresponding feature. When migrating from Oracle to Snowflake, any logic that relies on ROWID will have to be rewritten so that a different means of addressing individual rows is used. For example, a primary key column can be used instead as long as it is enforced by additional programming logic.

Updating data through a view

Oracle allows inserts, updates and deletes to be executed against a view, which will in turn update the underlying table. In Snowflake, you must always execute inserts, updates and deletes against a table but not through a view. Any code in Oracle that performs inserts, updates or deletes against a view – and this includes in-line views within queries – will have to be rewritten in Snowflake so that it performs the required operation on underlying tables.

Date arithmetic

Oracle’s date arithmetic allows adding and subtracting dates. For example, we can subtract one date from another date to get the number of days between dates. In Snowflake, in order to do such date arithmetic, you must use the DATEDIFF function, which takes three parameters and, in addition to the two dates, also a parameter that defines the unit (for example day, hour, etc.) of the resulting difference.

Oracle can also add or subtract integers from dates, which defaults to adding or subtracting days from a given date. This syntax is also not supported in Snowflake, instead you must use the DATEADD function to add or subtract units from dates or timestamps in Snowflake.

Since the DATE data type in Oracle also stores both the date and the time part, the function TRUNC is often used to retrieve only the date part. Although Snowflake also supports the TRUNC function on DATE and TIMESTAMP data types, it requires an additional parameter that specifies the unit (for example, the day, hour, etc.) to which the date or timestamp is to be truncated. When migrating Oracle to Snowflake, the TRUNC function will have to be amended with the unit parameter.

Query optimization hints

Query optimization hints that are often used in Oracle are not relevant in Snowflake because they are meant for the Oracle optimizer, which is different from the way that Snowflake optimizes queries. Technically, hints don’t have to be removed when migrating from Oracle to Snowflake because they will appear as comments to Snowflake and will therefore be ignored. But for better code readability it might be helpful to remove any hints during migration.

As a final note, the DUAL table is a special one-row, one-column table often used in Oracle for selecting pseudo columns. Snowflake also supports the DUAL table so any code that uses this table in Oracle will also run in Snowflake.

As is evident from the above examples, in addition to adhering to the ANSI SQL standard, there are still a number of differences to be aware of when migrating from Oracle to Snowflake. Some of these differences can be resolved fairly easily using mostly the search and replace functionality. Others will require more effort when the programming logic needs to be rewritten. There is no standard recipe on how to migrate SQL code from Oracle to Snowflake since each migration will be different, depending on how extensive Oracle specifics were used in the SQL.

Maja Ferle, Senior Consultant

If you need advice on your project, don’t hesitate to contact us.

Stay tuned for Part four – connect with us on LinkedIn.