Core Platform

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 RESULTSET is 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 IMMEDIATE statement 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.

Share Article

Subscribe to our blog newsletter

Get the best, coolest and latest delivered to your inbox each week

Where Data Does More

  • 30-day free trial
  • No credit card required
  • Cancel anytime