SSC-EWI-OR0110

For Update Clause is not supported in Snowflake

Severity

High

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

Description

There is no equivalent for FOR UPDATE clause in Snow Scripting so an EWI is added and the clause is commented out

Example Code

Input Code:

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE for_update_clause
AS
    update_record f_employee%rowtype;
    CURSOR c1 IS SELECT * FROM f_employee FOR UPDATE OF employee_number nowait;
BEGIN
    FOR CREC IN C1 LOOP
	UPDATE f_employee SET employee_number = employee_number + 1000 WHERE CURRENT OF c1;
	IF crec.id = 2 THEN
	    DELETE FROM f_employee WHERE CURRENT OF c1;
	    EXIT;
	END IF;
    END LOOP;
END;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE for_update_clause ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	DECLARE
		update_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
		c1 CURSOR
		FOR
			SELECT * FROM
				f_employee
			!!!RESOLVE EWI!!! /*** SSC-EWI-OR0110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
			FOR UPDATE OF employee_number nowait;
	BEGIN
		OPEN C1;
		--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
		FOR CREC IN C1 DO
			!!!RESOLVE EWI!!! /*** SSC-EWI-OR0136 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
			UPDATE f_employee
				SET employee_number =
 				                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! employee_number + 1000 WHERE CURRENT OF c1;
			IF (crec.id = 2) THEN
--				--** SSC-FDM-0024 - FUNCTIONALITY FOR 'CURRENT OF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--				DELETE FROM
--					f_employee
--				WHERE CURRENT OF c1
				                   ;
				EXIT;
			END IF;
		END FOR;
		CLOSE C1;
	END;
$$;

Related EWIS

1. SSC-EWI-0036: Data type converted to another data type.

2. SSC-PRF-0004: This statement has usages of cursor for loop.

3. SSC-EWI-OR0136: Current of clause is not supported in Snowflake.

4. SSC-EWI-0ROO36: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

5. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting

Recommendations

• Handle the column update in the UPDATE/DELETE query for more details check SSC-EWI-0075.

• If you need more support, you can email us at [e-mail]