MSCEWI3122

Package Variable Assignment transformation

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

A Package Variable assignment was transformed to a custom UDF in order to keep the functional equivalence. A TO_VARCHAR function is applied to the right expression of the assignment because all the package variables are declared as strings.

Example Code

Input Code:

-- PACKAGE DECLARATION
CREATE OR REPLACE PACKAGE PKG AS
    PKG_VAR1 INTEGER := 100;
	PROCEDURE P1;
END PKG;

-- PACKAGE BODY DEFINITION
CREATE OR REPLACE PACKAGE BODY PKG AS
  PROCEDURE P1 IS
  BEGIN
    -- PACKAGE VARIABLE ASSIGNMENT
    PKG_VAR1 := 40;
  END P1;
END PKG;
/

Output Code:

-- PACKAGE DECLARATION
CREATE SCHEMA IF NOT EXISTS PKG;

SET "PKG.PKG_VAR1" = '' || (100);

/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*	PROCEDURE P1;*/;

-- PACKAGE BODY DEFINITION
CREATE OR REPLACE PROCEDURE PKG.P1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        -- PACKAGE VARIABLE ASSIGNMENT
        /*** MSC-WARNING - MSCEWI3122 - PACKAGE VARIABLE ASSIGNMENT WAS TRANSFORMED TO CUSTOM UDF ***/
        UPDATE_PACKAGE_VARIABLE_STATE('PKG.PKG_VAR1', TO_VARCHAR(40));
    END;
$$;

Recommendations

• Check if the format parameter of the TO_VARCHAR function is required for the scenario.

• For DATE, DATETIME, TIME, or TIMESTAMP data types check the DATE_INPUT_FORMAT and TIMESTAMP_INPUT_FORMAT session parameters. Those values will affect the TO_VARCHAR function's correct execution.

• If you need more support, you can email us at [email protected]