Bridging the Gap: Migrating Oracle Cursor Variables Smoothly to Snowflake
As companies transition from traditional database systems such as Oracle to cloud-native platforms such as Snowflake, one critical aspect is rewriting stored procedures. These procedures, often the backbone of business applications, frequently rely on language elements specific to their original platform, and they are often deeply integrated into the fabric of core business applications, automating critical processes and encapsulating complex business logic.
However, migrating these procedures isn't always a straightforward process. One of the key challenges we encounter is the translation of specific language elements. For instance, Oracle PL/SQL makes extensive use of cursor variables for handling result sets in stored procedures. These cursor variables provide a powerful and flexible way to process data row by row. When migrating to Snowflake Scripting, the equivalent approach requires adapting to a different syntax and methodology.
Understanding Oracle PL/SQL cursor variables
In Oracle PL/SQL, stored procedures can declare cursor variables that provide flexibility beyond static queries. Unlike standard cursors that are bound to a specific SQL statement at compile time, a cursor variable acts as a pointer. It stores the address of a data structure, effectively referencing a result set, rather than containing the query itself. This dynamic nature allows cursor variables to be opened for different queries during runtime, enabling more adaptable and reusable procedural logic.
Cursor variables can also reference queries defined in string variables using dynamic SQL, enabling developers to define and execute queries with varying values at runtime.
Let's review how a cursor variable can be declared and used in a stored procedure:
CREATE OR REPLACE PROCEDURE get_department_salary(department_name VARCHAR2)
AS
-- Declare the cursor variable.
cursor_var SYS_REFCURSOR;
total_salary NUMBER := 0;
temp_salary NUMBER;
department_query VARCHAR2(50);
BEGIN
department_query := 'SELECT salary FROM employees WHERE department = :1';
-- Open a cursor with a query.
OPEN cursor_var FOR department_query USING (department_name);
LOOP
FETCH cursor_var INTO temp_salary;
EXIT WHEN cursor_var%NOTFOUND;
total_salary := total_salary + temp_salary;
END LOOP;
CLOSE cursor_var;
INSERT INTO department_salaries(department, salary)
VALUES (department_name, total_salary);
END;Challenges of migrating cursor variables to Snowflake
Snowflake Scripting does not provide native functionality for cursor variables in stored procedures; it only supports regular cursors, which are bound to specific queries at runtime.
| Feature | Oracle Cursor Variables | Snowflake Scripting Native Cursors |
|---|---|---|
| Query Definition | Associated with a query when the cursor variable is opened. | Defined directly within the cursor declaration. |
| Flexibility | More flexible. Can be opened for different queries at runtime. | Less flexible. The query and data structure are fixed. |
| Reusability | More reusable for processing various result set structures. | It is not reusable for different data structures. |
| Parameterization | The query used to open it can be constructed dynamically and include parameters. | Cannot directly use a query that was built dynamically. |
Migrating cursor variables to Snowflake Scripting
Let’s take a look at a practical example of how a stored procedure with cursor variables can be converted to Snowflake Scripting native cursors.
CREATE OR REPLACE PROCEDURE get_department_salary (department_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
-- Declare the cursor variable.
cursor_var_res RESULTSET;
total_salary NUMBER(38, 18) := 0;
temp_salary NUMBER(38, 18);
department_query VARCHAR(50);
BEGIN
department_query := 'SELECT salary FROM employees WHERE department = ?';
-- Open a cursor with a query.
cursor_var_res := (
EXECUTE IMMEDIATE :department_query USING (department_name)
);
LET cursor_var CURSOR FOR cursor_var_res;
OPEN cursor_var;
LOOP
FETCH cursor_var INTO :temp_salary;
IF (temp_salary IS NULL) THEN
EXIT;
END IF;
total_salary := :total_salary + :temp_salary;
END LOOP;
CLOSE cursor_var;
INSERT INTO department_salaries(department, salary)
SELECT
:department_name,
:total_salary;
END;
$$;Notice that in the Snowflake Scripting code, many new statements have been added by SnowConvert AI to mimic the cursor variable. Here is how SnowConvert AI can preserve functional equivalence when converting stored procedures to Snowflake Scripting:
RESULTSET variable declaration: A variable of type
RESULTSETis added to store the results of the query that the cursor will reference.Dynamic query execution: The variable with the result set is assigned to the result of an
EXECUTE IMMEDIATEstatement with the dynamic query and the values that will be bound to the placeholders in the query.Cursor declaration and assignment: An inline cursor is created with a reference to the result set that was created in the previous statements.
Handling cursor reassignments
There might be cases where a cursor variable is reassigned in a stored procedure to a different query. Even though this is not possible in Snowflake Scripting, SnowConvert AI can help identify these challenging patterns in the converted code through Error, Warning or Issue messages, also known as EWIs. This facilitates the conversion process by notifying the migration engineer of a specific challenge that needs to be solved. Let’s take a look at how SnowConvert AI can help identify these cases.
Oracle code:
CREATE OR REPLACE PROCEDURE my_procedure
AS
query1 VARCHAR(200) := 'SELECT 123 FROM dual';
query2 VARCHAR(200) := 'SELECT 456 FROM dual';
my_cursor_variable SYS_REFCURSOR;
BEGIN
OPEN my_cursor_variable FOR query1;
OPEN my_cursor_variable FOR query2;
END;Snowflake Scripting code:
CREATE OR REPLACE PROCEDURE my_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
query1 VARCHAR(200) := 'SELECT 123 FROM dual';
query2 VARCHAR(200) := 'SELECT 456 FROM dual';
my_cursor_variable_res RESULTSET;
BEGIN
my_cursor_variable_res := (
EXECUTE IMMEDIATE :query1
);
LET my_cursor_variable CURSOR
FOR
my_cursor_variable_res;
OPEN my_cursor_variable;
my_cursor_variable_res := (
EXECUTE IMMEDIATE :query2
);
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0133 - THE CURSOR VARIABLE NAMED 'my_cursor_variable' HAS ALREADY BEEN ASSIGNED IN ANOTHER CURSOR ***/!!!
LET my_cursor_variable CURSOR
FOR
my_cursor_variable_res;
OPEN my_cursor_variable;
END;
$$;Notice that SnowConvert AI has added a comment with a message indicating that the second cursor assignment must be renamed to a variable since there is a cursor that already uses that identifier name. These generated comments let the user know that there must be some manual work done before migrating the object successfully to Snowflake.
Automate Oracle PL/SQL code conversion with SnowConvert AI
We've explored the intricacies of migrating Oracle cursor variables to Snowflake Scripting, highlighting the challenges and solutions for these syntax elements. While understanding the differences between both of the procedural languages is important, manually converting all of these procedures in a big data warehouse consumes a lot of time and the process might be prone to many errors.
Leveraging the power of SnowConvert AI can dramatically simplify and accelerate your migration journey to Snowflake. SnowConvert AI allows you to shift your focus from tedious code conversion to leveraging the full potential of your Snowflake environment, accelerating your modernization journey and driving business value.
Download SnowConvert AI now and start your migration journey to Snowflake.
Author
