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 .

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

PreviousMSCEWI3107 NextMSCEWI3109

Last updated 1 year ago

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;

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