MSCEWI3108

The Following Assignment Statement is Not Supported by Snowflake Scripting

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

Severity

Medium

Description

Some Oracle variable types do not have a direct translation in Snowflake. Currently, transformation for cursor, collection, record, and user-defined type variables; as well as placeholders, objects, and output parameters are not supported by Snow Scripting.

Changing these variables to Snowflake semi-structured data types could help as a workaround in some scenarios.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE pinvalidassign(out_parameter   IN OUT NUMBER)
AS
record_variable       employees%ROWTYPE;

TYPE cursor_type IS REF CURSOR;
cursor1   cursor_type;
cursor2   SYS_REFCURSOR;

TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
collection_variable     collection_type;

BEGIN
--Record Example
  record_variable.last_name := 'Ortiz';

--Cursor Example
  cursor1 := cursor2;
  
--Collection
  collection_variable('Test') := 5;

--Out Parameter
  out_parameter := 123;
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.pinvalidassign(out_parameter FLOAT)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      record_variable VARIANT;

-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE cursor_type IS REF CURSOR;
      cursor1 VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'cursor_type' NOT FOUND ***/;
      cursor2 VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'SYS_REFCURSOR' NOT FOUND ***/;

-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
      collection_variable VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'collection_type' NOT FOUND ***/;
   BEGIN
-- ** MSC-ERROR - MSCEWI3108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      record_variable.last_name := 'Ortiz'
                                          ;
-- ** MSC-ERROR - MSCEWI3108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      cursor1 := cursor2
                        ;
-- ** MSC-ERROR - MSCEWI3108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      collection_variable('Test') := 5
                                      ;
-- ** MSC-ERROR - MSCEWI3108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      out_parameter := 123
                          ;
   END;
$$;

Recommendations

• Change the variable data type or try to simulate the behavior using Snowflake semi-structured data types.

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