Migrating Oracle PL/SQL Stored Procedures with Output Arguments to Snowflake

A new chapter has begun in the Snowflake ecosystem for data engineers and developers. On July 2, 2025, Snowflake's 9.18 release introduced a long-awaited feature: support for OUT parameters in stored procedures. This update marks a significant leap forward, particularly for those migrating from platforms such as Oracle, where OUT parameters are a staple. The popular SnowConvert AI tool was quick to adapt, integrating this new grammar in order to streamline code conversion and drastically reduce manual effort.

This post will explore the ins and outs of OUT arguments, which provide a mechanism for returning data from a specific step in a stored procedure. We'll examine the various use cases in Oracle and determine which can be directly mapped to Snowflake Scripting. We will also highlight how SnowConvert AI is crucial for handling complex scenarios to ensure seamless behavior on the Snowflake platform. Ultimately, you'll gain a clear understanding of how this powerful new feature simplifies migration and unlocks new possibilities for your data workloads.

How do output arguments in Oracle PL/SQL work?

Oracle's basic output argument scenarios are trivial. Let's see an example of how the code looks:

CREATE OR REPLACE PROCEDURE get_employee_info
(
   p_emp_id   INT,
   p_name     OUT VARCHAR2,
   p_salary   OUT NUMBER
)
   IS
BEGIN
   SELECT emp_name, salary
   INTO   p_name,  p_salary
   FROM   employees
   WHERE  emp_id = p_emp_id;
END;
/

The code above performs a SELECT query that sets the OUT arguments with the EMP_NAME and SALARY columns where the EMP_ID matches the given argument.

Now let's see what the converted code looks like:

CREATE OR REPLACE PROCEDURE get_employee_info
(
   p_emp_id INT, 
   p_name OUT VARCHAR, 
   p_salary OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
     SELECT emp_name, salary
     INTO
        :p_name,
        :p_salary
     FROM
        employees
     WHERE  emp_id = :p_emp_id;
  END;
$$;

Data type support in Snowflake

Let's see a comparative table of which data types are supported in OUT arguments in both languages.

DATA TYPE	ORACLE	SNOWFLAKE
Primitives (`INT`, `VARCHAR`, etc.)	Supported	Supported
Cursors	Supported	Supported with SnowConvert AI conversion workaround
Records	Supported	Supported with SnowConvert AI conversion workaround
User-Defined Types (UDT)	Supported	Data type transformed to `VARIANT`, but the result might not be correct. Snowflake doesn't natively support user-defined types.
%TYPE Attribute	Supported	Supported with SnowConvert AI conversion workaround

SnowConvert AI transformations for unsupported `OUT` data types

Cursors

Cursor translations are handled by SnowConvert AI by creating a dynamic name temporary table within the original cursor query. Let's see an Oracle example:

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
   p_department_id IN NUMBER,
   p_employee_cursor OUT SYS_REFCURSOR
)
as
BEGIN
   OPEN p_employee_cursor FOR
       SELECT employee_id, first_name, last_name
       FROM   employees_sample
       WHERE  department_id = p_department_id
       ORDER BY last_name;
END get_employees_by_dept;
/

The first step is to change the SYS_REFCURSOR or the cursor UDT to a VARCHAR type. Notice how the TEMPORARY TABLE's name is now being created dynamically with the argument that was previously the cursor (now a VARCHAR) and the cursor's query is now part of the TEMPORARY TABLE's body:

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
  p_department_id NUMBER(38, 18),
  p_employee_cursor VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
 BEGIN
   CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:p_employee_cursor) AS
     SELECT
       employee_id,
       first_name,
       last_name
     FROM
       employees_sample
     WHERE
       department_id = :p_department_id
     ORDER BY last_name;
 END;
$$;

In Oracle, calling a stored procedure with cursor OUT arguments is the same as invoking stored procedures with native types as output arguments. Let's see:

CREATE OR REPLACE PROCEDURE proc_calling_proc_with_cursor()
AS
DECLARE
   l_emp_id NUMBER;
   l_first_name VARCHAR;
   l_last_name VARCHAR;
   l_cursor  SYS_REFCURSOR;
BEGIN
   get_employees_by_dept(10, l_cursor);
   LOOP
       FETCH l_cursor INTO l_emp_id, l_first_name, l_last_name;
       EXIT WHEN l_cursor%NOTFOUND;
       INSERT INTO employee VALUES (l_emp_id, l_first_name, l_last_name);
   END LOOP;
   CLOSE l_cursor;
END;

In Snowflake, the invocation of the stored procedure also needs to change. The cursor parameter is now a VARCHAR containing the caller procedure name concatenated with the cursor variable name.

CREATE OR REPLACE PROCEDURE proc_calling_proc_with_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
 DECLARE
   l_emp_id NUMBER(38, 18);
   l_first_name VARCHAR;
   l_last_name VARCHAR;
 BEGIN
   CALL get_employees_by_dept(10, 'proc_calling_proc_with_cursor_l_cursor');
   LET l_cursor CURSOR
   FOR
     SELECT
       *
     FROM
       IDENTIFIER('proc_calling_proc_with_cursor_l_cursor');
   OPEN l_cursor;
   LOOP
     FETCH
       l_cursor
     INTO
       :l_emp_id,
       :l_first_name,
       :l_last_name;
     IF (l_emp_id IS NULL) THEN
       EXIT;
     END IF;
     INSERT INTO employee
     SELECT
       :l_emp_id,
       :l_first_name,
       :l_last_name;
   END LOOP;
   CLOSE l_cursor;
 END;
$$;

Records

According to Oracle's documentation, records are composite variables that can store data values of different types. Let's see the following Oracle PL/SQL code:

CREATE OR REPLACE PROCEDURE procedure_with_out_params(
 param1 OUT INTEGER,
 param2 OUT INTEGER)
IS
BEGIN
 param1 := 123;
 param2 := 456;
END;

CREATE OR REPLACE PROCEDURE procedure_with_records
IS
 TYPE custom_record1 IS RECORD(field3 INTEGER, field4 INTEGER);
 TYPE custom_record2 IS RECORD(field1 INTEGER, field2 custom_record1);
 var1 custom_record2;
BEGIN
 procedure_with_out_params(var1.field1, var1.field2.field4);
END;

The code below shows how Oracle records are translated when used as output parameters. SnowConvert AI generates each field of the record structure in the declaration section as individual variables, assigns the output argument to each variable, and then inserts the variable value in a new object construct that emulates the record behavior. Let’s see:

CREATE OR REPLACE PROCEDURE procedure_with_out_params (
param1 OUT INTEGER, 
param2 OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
 BEGIN
   param1 := 123;
   param2 := 456;
 END;
$$;

CREATE OR REPLACE PROCEDURE procedure_with_records ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
 DECLARE
   var1 OBJECT := OBJECT_CONSTRUCT();
   var1_field1 INTEGER;
   var1_field2_field4 INTEGER;
 BEGIN
   CALL procedure_with_out_params(:var1_field1, :var1_field2_field4);
   var1 := OBJECT_INSERT(COALESCE(var1, OBJECT_CONSTRUCT()), 'field1', :var1_field1, true);
   var1 := OBJECT_INSERT(COALESCE(var1, OBJECT_CONSTRUCT()), 'field2', OBJECT_INSERT(COALESCE(var1:field2, OBJECT_CONSTRUCT()), 'field4', :var1_field2_field4, true), true);
 END;
$$;

Note: Some conversion issues (SSC-EWI-0056 and SSC-EWI-0036 ) are omitted for clarity reasons. In this context, they would not affect the output code, so they can be removed.

Automate Oracle PL/SQL code conversion with SnowConvert AI

The journey of migrating from Oracle to Snowflake can be complex, but with the introduction of OUT parameters and the power of SnowConvert AI, the path is now clearer than ever. By automating the conversion of intricate Oracle PL/SQL, SnowConvert AI eliminates a significant source of manual effort and potential errors. To accelerate your migration and take full advantage of Snowflake's enhanced capabilities, download SnowConvert AI today.