Back

Convert Oracle code to Snowflake

8.03.2023

Oracle to Snowflake Migration Series, part 4

Convert Oracle code to Snowflake: By converting code, we are referring to Oracle PL/SQL procedures, functions, and packages that will be migrated. Since Snowflake supports SQL scripting, many of the programming constructs will migrate from Oracle without much change. You can write BEGIN … EXCEPTION … END blocks in Snowflake, just like in Oracle, but there are still subtle differences in how you declare variables, pass and return parameters, call the stored procedures and other details.

While you can always manually examine and rewrite all of the code, we have another way. We use our in-house tool Parsaur to do the initial code conversion.

Parsaur – Code Conversion and Lineage tool

Faster migration of 1000s of existing views, SQL statements and procedures to Snowflake.

Parsaur translates code from one SQL dialect to Snowflake and converts the source platform-specific capabilities and features that are the toughest nut to crack. Automating otherwise manual processes can shorten the conversion time by a minimum of 80% and provide standardization, greater code consistency, and lower error probability.

Translating an existing SQL table DDL statement to Snowflake is something you can easily do with domain knowledge and the “Find + Replace” functionality in Notepad++. On the other hand, converting a particular SQL stored procedure or PLSQL package to the Snowflake platform can be a much more difficult problem. The ability to solve such challenges represents the core on which Parasaur was built.

Convert Oracle code to Snowflake with Parsaur 

Parsaur is an indispensable tool in any migration project. By automating otherwise manual processes, it brings standardization, greater code consistency and lower error probability, requiring fewer development resources and enabling faster migration.

We have tweaked Parsaur to be able to convert Oracle peculiarities into Snowflake syntax. Some examples are described below.

Updating data through a view 

One example of how Parsaur converts code is when we update 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 – which includes in-line views within queries – will have to be rewritten in Snowflake so that it performs the required operation on underlying tables.  This is illustrated in the following code snippet: 

Oracle:

UPDATE (
  SELECT cust.end_dttm, utl_ld.end_dttm end_dttm_new
  FROM cust, utl_ld
  WHERE cust.cust_id = utl_ld.cust_id
)
SET end_dttm =end_dttm_new;

Snowflake:

UPDATE cust
SET end_dttm = utl_ld.end_dttm
FROM utl_ld
WHERE cust.cust_id = utl_ld.cust_id;

Date arithmetic

There are differences between Snowflake and Oracle in how they each perform date arithmetic. Here are some examples:

• To calculate the number of days between dates, we can subtract the two dates in Oracle, but we must use the DATEDIFF() function in Snowflake
• To add a certain number of days to a date, we can use the plus operator in Oracle, i.e. + 1, but we must use the DATEADD() function in Snowflake
• To remove the time part from a DATE value, we can use the TRUNC() function in Oracle, but in Snowflake, the TRUNC() function takes an additional parameter that tells you to which unit it has been truncated, i.e. DAY

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 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, Parsaur can remove hints during migration.

DUAL table

The DUAL table is a special one-row, the 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. However, to remove Oracle-specific code – even if it is supported in Snowflake for compatibility – when migrating to Snowflake, Parsaur can remove the “from DUAL” clause from such queries since Snowflake doesn’t require it.

These are just a few examples of how Parsaur converts code and what differences we may encounter that must be addressed during migration.

However, in terms of automatic code conversion, there is only so much that Parsaur can do. There are other differences between Oracle and Snowflake as well that require manual code analysis and possible re-engineering.

In the next section, we will discuss migrating functions, procedures and packages. Stay tuned!

---