Some statements like UPDATE and DELETE can use have a CURRENT OF clause inside the WHERE clause, this is not currently supported by Snowflake.
Example Code
Oracle:
IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE proc_update_current_of
AS
CURSOR C1
IS
SELECT * FROM F_EMPLOYEE FOR UPDATE OF SALARY nowait;
BEGIN
FOR CREC IN C1
LOOP
UPDATE F_EMPLOYEE SET SALARY=SALARY+2000 WHERE CURRENT OF C1;
END LOOP;
END;
Snowflake Scripting:
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE proc_update_current_of ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
C1 CURSOR
FOR
SELECT * FROM
F_EMPLOYEE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0110 - FOR UPDATE CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR UPDATE OF SALARY 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 SALARY=
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!SALARY+2000 WHERE CURRENT OF C1;
END FOR;
CLOSE C1;
END;
$$;
Related EWI
Recommendations
Redesign the query to normal UPDATE or DELETE specifying the columns in the WHERE clause, consider that if there are duplicate records in the table the query can affect them multiple times.
