SSC-EWI-OR0136

Current of clause is not supported in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

Critical

Description

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
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "F_EMPLOYEE" **
CREATE OR REPLACE PROCEDURE proc_update_current_of ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    --** SSC-PRF-0009 - PERFORMANCE REVIEW - CURSOR USAGE **
    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;
$$;
  1. SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.

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

  3. SSC-EWI-OR0110: For Update Clause is not supported in Snowflake.

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.

  • If you need more support, you can email us at [email protected]

Last updated