OUTPUT PARAMETERS

This article is about the current transformation of the output parameters and how their functionality is being emulated.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

An output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. Since the output parameters are not supported by Snowflake Scripting, a solution has been implemented in order to emulate their functionality.

Sample Source Patterns

Single out parameter

Oracle

Query

IN -> Oracle_02.sql

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters(param1 OUT NUMBER)
IS
BEGIN
    param1 := 123;
END;

-- Procedure with output parameter being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters
IS
    var1 NUMBER;
BEGIN
    proc_with_single_output_parameters(var1);
    INSERT INTO TABLE01 VALUES(var1, -1);
END;

Snowflake Scripting

Query

OUT -> Oracle_02.sql

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters (param1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
    END;
$$;

-- Procedure with output parameter being called
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE01" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
    BEGIN
        CALL
        proc_with_single_output_parameters(:var1);
        INSERT INTO TABLE01
        VALUES(:var1, -1);
    END;
$$;

Multiple out parameter

Oracle

Query

IN -> Oracle_03.sql

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE proc_with_multiple_output_parameters(
    param1 OUT NUMBER,
    param2 IN OUT NUMBER
)
IS
BEGIN
    param1 := 123;
    param2 := 456;
END;

-- Procedure with output parameters being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_multiple_output_parameters
IS
    var1 NUMBER;
    var2 NUMBER;
BEGIN
    proc_with_multiple_output_parameters(var1, var2);
    INSERT INTO TABLE01 VALUES(var1, var2);
END;

Snowflake Scripting

Query

OUT -> Oracle_03.sql

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE proc_with_multiple_output_parameters (param1 OUT NUMBER(38, 18), param2 OUT NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
        param2 := 456;
    END;
$$;

-- Procedure with output parameters being called
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "TABLE01" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
        var2 NUMBER(38, 18);
    BEGIN
        CALL
        proc_with_multiple_output_parameters(:var1, :var2);
        INSERT INTO TABLE01
        VALUES(:var1, :var2);
    END;
$$;

In order to check that the functionality is being emulated correctly the following query is going to execute the procedure and a SELECT from the table mentioned before.

Oracle

Query

IN -> Oracle_04.sql

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM table01;

Result

Snowflake Scripting

Query

OUT -> Oracle_04.sql

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM table01;

Result

Customer data type OUT parameters

When the output parameter is a customer type, the process is similar to a regular data type.

Oracle

IN -> Oracle_05.sql

CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (
    p_employee_id NUMBER,
    p_address OUT address_type
)
AS
BEGIN
    -- Retrieve the employee's address based on the employee ID.
    SELECT home_address INTO p_address
    FROM employees
    WHERE employee_id = p_employee_id;
END;

Snowflake Scripting

OUT -> Oracle_05.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "address_type", "employees" **
CREATE OR REPLACE PROCEDURE procedure_udtype_out_params (p_employee_id NUMBER(38, 18), p_address OUT VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'address_type' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        -- Retrieve the employee's address based on the employee ID.
        SELECT home_address INTO
            :p_address
        FROM
            employees
        WHERE employee_id = :p_employee_id;
    END;
$$;

Cursor OUT parameters

Cursor out parameters are not supported in Snowflake; despite that, a workaround that emulates Oracle's behavior is applied to the transformed code. The procedure with the out parameters generates a temporary table with a dynamic name, and the procedure call will define the name of the temp table as a string to create the table within the procedure call.

Oracle

IN -> Oracle_06.sql

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;
/
               
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;
/

Snowflake Scripting

OUT -> Oracle_06.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees_sample" **
CREATE OR REPLACE PROCEDURE get_employees_by_dept (p_department_id NUMBER(38, 18), p_employee_cursor VARCHAR
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
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;
$$;

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employee" **
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_cursor ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
 DECLARE
    l_emp_id NUMBER(38, 18);
    l_first_name VARCHAR;
    l_last_name VARCHAR;
    l_cursor_res RESULTSET;
 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;
    --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
   --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        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;
$$;

Record OUT parameters

Records are not natively supported in Snowflake; however, a workaround was used to emulate them as output parameters. By defining an OBJECT variable instead of the record, we could emulate the record's field structure by assigning the out parameter result to each object property. Additionally, for each record field assigned as an out parameter, a new variable with the field type will be generated.

Oracle

IN -> Oracle_07.sql

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 test_proc
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;

Snowflake Scripting

OUT -> Oracle_07.sql

CREATE OR REPLACE PROCEDURE procedure_with_out_params (param1 OUT INTEGER, param2 OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  BEGIN
    param1 := 123;
    param2 := 456;
  END;
$$;

CREATE OR REPLACE PROCEDURE test_proc ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
    TYPE custom_record1 IS RECORD(field3 INTEGER, field4 INTEGER);
    !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
    TYPE custom_record2 IS RECORD(field1 INTEGER, field2 custom_record1);
    var1 OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - custom_record2 DATA TYPE CONVERTED TO 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;
$$;

Package Variables as OUT parameters

Packages are not supported in Snowflake, so their local members, like variables or constants, should also be preserved using a workaround. In this scenario, the package variable would be emulated using a session variable that would be updated after setting a local variable with the output parameter result.

Oracle

IN -> Oracle_08.sql

CREATE OR REPLACE PACKAGE scha1.pkg1 AS
    PKG_VAR1 NUMBER;
END my_package;
/

CREATE OR REPLACE PROCEDURE PROC_WITH_OUT_PARAM(param1 OUT NUMBER)
AS
BEGIN
   param1 := 0;
END;
CREATE OR REPLACE PROCEDURE PROC ()
AS
BEGIN 
   PROC_WITH_OUT_PARAM(param1 => scha1.pkg1.PKG_VAR1);
END;

Snowflake Scripting

OUT -> Oracle_08.sql

CREATE SCHEMA IF NOT EXISTS SCHA1_PKG1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
;

SET "SCHA1_PKG1.PKG_VAR1" = '~';

CREATE OR REPLACE PROCEDURE PROC_WITH_OUT_PARAM (param1 OUT NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   BEGIN
      param1 := 0;
   END;
$$;

CREATE OR REPLACE PROCEDURE PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      SCHA1_PKG1_PKG_VAR1 VARIANT;
   BEGIN
      CALL
      PROC_WITH_OUT_PARAM(param1 => :SCHA1_PKG1_PKG_VAR1);
      CALL UPDATE_PACKAGE_VARIABLE_STATE_UDF('SCHA1_PKG1.PKG_VAR1', TO_VARCHAR(:SCHA1_PKG1_PKG_VAR1));
   END;
$$;

Known Issues

1. Procedures with output parameters inside packages may not work correctly

Currently, there is an issue collecting the semantic information of procedures that reside inside packages, which is why the transformation for output parameters may work partially or not work at all. There is already a work in progress to resolve this issue.

2. Some data types may not work properly

As seen in the transformation, when retrieving the value from the called procedures, an implicit cast is performed from VARIANT to the type specified by the variable. Since there are a lot of possible data types, some casts may fail or contain different data.

Related EWIs

1. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.

2. SSC-FDM-0007: Element with missing dependencies.

3. SSC-FDM-0015: Data Type Not Recognized.