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

Sample auxiliary table

IN -> Oracle_01.sql

CREATE TABLE table01 (col1 NUMBER, col2 NUMBER);

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table01 (col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

In the declaration, the OUT or IN OUT keywords are removed. The assignment is being emitted the same as the input but to emulate the functionality of the output parameter some statements are being added.

When a procedure with output parameters is being called into another one, some statements are added in order to get and assign the value(s) to the respective argument(s).

Single out parameter

Oracle

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

OUT -> Oracle_02.sql

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters (param1 NUMBER(38, 18)
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
        RETURN param1;
    END;
$$;

-- Procedure with output parameter being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
        call_results VARIANT;
    BEGIN
        call_results := (
            CALL
            proc_with_single_output_parameters(:var1)
        );
        var1 := :call_results;
        INSERT INTO TABLE01
        VALUES(:var1, -1);
    END;
$$;

Multiple out parameter

Oracle

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

OUT -> Oracle_03.sql

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE proc_with_multiple_output_parameters (param1 NUMBER(38, 18), param2 NUMBER