OUTPUT PARAMETERS
This article is about the current transformation of the output parameters and how their functionality is being emulated.
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
-- 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
-- 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
-- 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
-- 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
CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();
SELECT * FROM table01;Snowflake Scripting
CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();
SELECT * FROM table01;Customer data type OUT parameters
When the output parameter is a customer type, the process is similar to a regular data type.
Oracle
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
--** 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
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
--** 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
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
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
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
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
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
SSC-FDM-0007: Element with missing dependencies.
SSC-FDM-0015: Data Type Not Recognized.
Last updated