SSC-EWI-0046

Nested function/procedure declarations are considered a complex pattern and not supported in snowflake.

Severity

Critical

Description

Snowflake does not support the declaration of nested functions/procedures, this warning is added to any create function or create procedure statement in which nested declarations were found.

Code example

Input

IN -> Oracle_01.sql

CREATE OR REPLACE FUNCTION myFunction
RETURN INTEGER
IS
   total_count INTEGER;
   -- Function Declaration
   FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER;
   FUNCTION function_definition
   RETURN INTEGER
   IS
   count INTEGER;
   PROCEDURE procedure_declaration(param1 INTEGER)
   IS
       BEGIN
            NULL;
       END;
  BEGIN
    RETURN count;
  end;
BEGIN
    -- Your logic to calculate the total employee count goes here
    RETURN total_count;
END;

Output

OUT -> Oracle_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0046 - NESTED FUNCTION/PROCEDURE DECLARATIONS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
CREATE OR REPLACE FUNCTION myFunction ()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "12/16/2024",  "domain": "test" }}'
AS
$$
  let TOTAL_COUNT;
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
  /*    -- Function Declaration
     FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; */
  // Function Declaration
  ;
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!!
  /*    FUNCTION function_definition
     RETURN INTEGER
     IS
     count INTEGER;
     PROCEDURE procedure_declaration(param1 INTEGER)
     IS
         BEGIN
              NULL;
         END;
    BEGIN
      RETURN count;
    end; */
  ;
  // Your logic to calculate the total employee count goes here
  return TOTAL_COUNT;
$$;

Recommendations

Remove the nested declarations from the function/procedure.

PreviousSSC-EWI-0045 NextSSC-EWI-0049

Last updated 4 months ago

CREATE OR REPLACE FUNCTION myFunction RETURN INTEGER IS total_count INTEGER; -- Function Declaration FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; FUNCTION function_definition RETURN INTEGER IS count INTEGER; PROCEDURE procedure_declaration(param1 INTEGER) IS BEGIN NULL; END; BEGIN RETURN count; end; BEGIN -- Your logic to calculate the total employee count goes here RETURN total_count; END;

!!!RESOLVE EWI!!! /*** SSC-EWI-0046 - NESTED FUNCTION/PROCEDURE DECLARATIONS ARE NOT SUPPORTED IN SNOWFLAKE. ***/!!! CREATE OR REPLACE FUNCTION myFunction () RETURNS FLOAT LANGUAGE JAVASCRIPT COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "12/16/2024", "domain": "test" }}' AS $$ let TOTAL_COUNT; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!! /* -- Function Declaration FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; */ // Function Declaration ; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ***/!!! /* FUNCTION function_definition RETURN INTEGER IS count INTEGER; PROCEDURE procedure_declaration(param1 INTEGER) IS BEGIN NULL; END; BEGIN RETURN count; end; */ ; // Your logic to calculate the total employee count goes here return TOTAL_COUNT; $$;