Oracle to Snowflake migration: Migrating SQL code5.11.2020
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.
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.
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.