ASSIGNMENT STATEMENT

Description

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

Some parts in the output code are omitted for clarity reasons.

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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE TASSIGN (
     COL1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL3 VARCHAR(20),
     COL4 VARCHAR(20)
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 CREATE OR REPLACE PROCEDURE PSCALAR ()
 RETURNS VARCHAR
 LANGUAGE SQL
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 EXECUTE AS CALLER
 AS
 $$
     DECLARE
     var1 NUMBER(38, 18) := 40;
     var2 NUMBER(38, 18) := 22.50;
     var3  VARCHAR(20);
     var4  BOOLEAN;
     var5 NUMBER(38, 18);
     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.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

IN -> Oracle_02.sql

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

OUT -> Oracle_02.sql

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees" **
CREATE OR REPLACE PROCEDURE pinvalid (out_parameter NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
--    --** SSC-FDM-0024 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **

--    TYPE cursor_type IS REF CURSOR;
    cursor1_res RESULTSET;
    cursor2_res RESULTSET;
--    --** SSC-FDM-0024 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **

--    TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_type' USAGE CHANGED TO VARIANT ***/!!!;
  BEGIN
    --Record Example
    record_variable := OBJECT_INSERT(record_variable, 'LAST_NAME', 'Ortiz', true);

    --Cursor Example
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      cursor1 := :cursor2;

    --Collection
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      collection_variable('Test') := 5;
    --Out Parameter
    out_parameter := 123;
    RETURN out_parameter;
  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. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
2. SSC-EWI-0036: Data type converted to another data type.
3. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
4. SSC-EWI-0062: Custom type usage changed to variant.
5. SSC-EWI-OR0108: The Following Assignment Statement is Not Supported by Snowflake Scripting.