ASSIGNMENT STATEMENT

Description

The assignment statement sets the value of a data item to a valid value. (Oracle PL/SQL Language Reference ASSIGNMENT Statement)

Oracle Assignment Syntax

assignment_statement_target := expression ;

assignment_statement_target = 
{ collection_variable [ ( index ) ]
| cursor_variable
| :host_cursor_variable
| object[.attribute]
| out_parameter
| placeholder
| record_variable[.field]
| scalar_variable
}

Snowflake Scripting Assignment Syntax

LET <variable_name> <type> { DEFAULT | := } <expression> ;

LET <variable_name> { DEFAULT | := } <expression> ;

LET keyword is not needed for assignment statements when the variable has been declared before. Check Snowflake Assignment documentation for more information.

Sample Source Patterns

1. Scalar Variables

Oracle

Query

CREATE TABLE TASSIGN
(
	COL1 NUMBER,
	COL2 NUMBER,
	COL3 VARCHAR(20),
	COL4 VARCHAR(20)
)


CREATE OR REPLACE PROCEDURE PSCALAR
AS
   var1  NUMBER := 40;
   var2  NUMBER := 22.50;
   var3  VARCHAR(20);
   var4  BOOLEAN;
   var5  NUMBER;
BEGIN
   var1 := 1;
   var2 := 2.1;
   var2 := var2 + var2;
   var3 := 'Hello World';
   var4 := true;
   var4 := var1 > 500;
   IF var4 THEN
      var5 := 0;
   ELSE
      var5 := 1;
   END IF;
  INSERT INTO TASSIGN VALUES(var1, var2, var3, var5);
END;

CALL PSCALAR();

SELECT * FROM TASSIGN;

Result

COL1|COL2|COL3       |COL4|
----+----+-----------+----+
   1| 4.2|Hello World|   1|

Snowflake Scripting

Query

CREATE OR REPLACE TABLE PUBLIC.TASSIGN (
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 ***/,
COL3 VARCHAR(20),
COL4 VARCHAR(20));

CREATE OR REPLACE PROCEDURE PUBLIC.PSCALAR ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      var1  NUMBER := 40;
      var2  NUMBER := 22.50;
      var3 STRING;
      var4  BOOLEAN;
      var5  NUMBER;
   BEGIN
      var1 := 1;
      var2 := 2.1;
      var2 := :var2 + :var2;
      var3 := 'Hello World';
      var4 := true;
      var4 := :var1 > 500;
      IF (:var4) THEN
         var5 := 0;
         ELSE
            var5 := 1;
      END IF;
      INSERT INTO PUBLIC.TASSIGN VALUES(:var1, :var2, :var3, :var5);
   END;
$$;

CALL PUBLIC.PSCALAR();

SELECT * FROM PUBLIC.TASSIGN;

Result

COL1                |COL2                |COL3       |COL4|
--------------------+--------------------+-----------+----+
1.000000000000000000|4.000000000000000000|Hello World|1   |

Transformation for some data types needs to be updated, it may cause different results. For example, NUMBER to NUMBER rounds the value and the decimal point is lost. There is already a work item for this issue.

2. Out Parameter Assignment

To get more information about how the output parameters are being converted, please go to the following article Output Parameters.

3. Not Supported Assignments

Oracle

Query

CREATE OR REPLACE PROCEDURE pinvalid(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;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.pinvalid(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;
$$;

Known Issues

1. Several Unsupported Assignment Statements

Currently, transformation for cursor, collection, record, and user-defined type variables are not supported by Snow Scripting. Therefore assignment statements using these variables are commented and marked as not supported. Changing these variables to Snowflake semi-structured data types could help as a workaround in some scenarios.

Related EWIs

1. MSCEWI3108: The Following Assignment Statement is Not Supported by Snowflake Scripting.