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

Oracle

CREATE TABLE table01 ( col1 NUMBER, col2 NUMBER );

Snowflake

CREATE OR REPLACE TABLE PUBLIC.table01(
col1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
);

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

Query

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

-- Procedure with output parameter declaration
CREATE OR REPLACE PROCEDURE proc_with_single_output_parameters (param1
/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/
NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        RETURN null;
    END;
$$;

/*** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "TABLE01" ***/

-- Procedure with output parameter being called
CREATE OR REPLACE PROCEDURE proc_calling_proc_with_single_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER(38, 18);
        call_results VARIANT;
    BEGIN
        call_results := (
            CALL
            proc_with_single_output_parameters(:var1)
        );
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        var1 := :call_results;
        INSERT INTO TABLE01
        VALUES(:var1, -1);
    END;
$$;

Multiple out parameter

Oracle

Query

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

-- Procedure with output parameters declaration
CREATE OR REPLACE PROCEDURE PUBLIC.proc_with_multiple_output_parameters (param1
/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/
NUMBER(38, 18), param2
/*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/
NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        param1 := 123;
        param2 := 456;
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);
    END;
$$;


-- Procedure with output parameters being called
CREATE OR REPLACE PROCEDURE PUBLIC.proc_calling_proc_with_multiple_output_parameters ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        var1 NUMBER (38,18);
        var2 NUMBER (38,18);
        call_results VARIANT;
        
    BEGIN
        call_results := (
            CALL PUBLIC.proc_with_multiple_output_parameters(:var1, :var2)
        );
        /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
        var1 := :call_results:param1;
        var2 := :call_results:param2;

        INSERT INTO PUBLIC.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

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM table01;

Result

COL1|COL2
----+----
123 |-1
123 |456

Snowflake Scripting

Query

CALL proc_with_single_output_parameters();
CALL proc_with_multiple_output_parameters();

SELECT * FROM PUBLIC.table01;

Result

COL1                   | COL2
-----------------------+-----------------------
123.000000000000000000 | -1
123.000000000000000000 | 456.000000000000000000

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

CCREATE OR REPLACE PROCEDURE procedure_udtype_out_params (p_employee_id NUMBER(38, 18), p_address
--** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED **
VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'address_type' USAGE CHANGED TO VARIANT ***/
)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    SELECT home_address:street :: VARCHAR AS street, home_address:city :: VARCHAR AS city, home_address:state :: VARCHAR AS state, home_address:postal_code :: VARCHAR AS postal_code INTO
      :p_address
     FROM
      employees
     WHERE employee_id = :p_employee_id;
    --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
    RETURN p_address;
  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. MSCEWI1082: Auto-Generated code in order to emulate the output parameters functionality.
2. MSCEWI1083: Output parameters are not supported by Snowflake Scripting but their functionality is being emulated.
3. MSCEWI1087: Arguments do not match the called procedure parameters count, default parameters are not supported yet.