Back

Migrating Oracle Functions, Procedures and Packages to Snowflake

5.04.2023

Oracle to Snowflake Migration Series, part 5

Global Variables in Packages

Since Snowflake doesn’t support packages, all procedures and functions can be migrated to stand-alone procedures and functions in Snowflake. This means that they will also be referenced differently as there will be no package identifier. In addition, packages may contain global variables that are shared across the procedures and functions within the package. Since there is no package object in Snowflake, the code must be re-engineered in order to be able to pass variables in a different way. In our migrations, we create a table that stores the global variables and their values. This table is then used by related stored procedures and functions to read and write the variable values.

Returned Values from Stored Procedures

In Snowflake, stored procedures can return values just like functions. This differs from Oracle, where stored procedures take input and output parameters as arguments to the stored procedure call. When migrating stored procedures that return one output parameter, you can rewrite them to use a return value just like in a function. However, when a stored procedure returns more than one output parameter, you will have to re-engineer the code to find a different solution for returning values, such as using a parameter table, similar to package global variables.

Returning Tabular Data

Just like in Oracle, both functions and stored procedures in Snowflake can return tabular data. Even the syntax to call the tabular functions is similar, so this part of the code will migrate without major changes.

Stored Procedure Privileges

Similar to Oracle, stored procedures in Snowflake 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. Snowflake supports defining procedures either with owner’s rights or caller’s rights. A procedure with owner’s rights can be used to access objects to which the caller doesn’t have access; however, the procedure encapsulates the logic that the caller is nevertheless allowed to see. This is similar to granting execute privileges on selected administration procedures in Oracle.

Constraints

Although Snowflake SQL syntax supports creating constraints such as primary key and foreign key, they are not physically enforced. Whenever you have any SQL code that relies on constraints to prevent duplicate entries and orphaned records, you will have to re-engineer the code in Snowflake.

ROWID Pseudocolumn

In addition to the primary key, another feature in Oracle is the ROWID pseudo column, 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 the individual rows are used. For example, a primary key column can be used instead. 

Rewrite Procedures as SQL Statements if Possible

One way to simplify the logic in the code while re-engineering is to explore the code to understand what the procedure logic is trying to accomplish and consider whether a procedure is really needed. We have seen this time and time again. It is perfectly possible to rewrite a procedure as an SQL statement, and in such cases, we can simplify the code tremendously. The reason for complicated logic in procedures that can be replaced with a SQL statement often lies in procedures that have been written ages ago, in earlier versions of Oracle that didn’t support window functions, or when Oracle was not as performant, so the stored procedures with cursors were written to implement logic or improve performance. When migrating to Snowflake, you don’t need to worry about performance too much, so we encourage you to really focus on understanding the code that is being migrated and try to simplify it by rewriting it as a SQL statement whenever possible.

---