SSC-EWI-0046

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

Severity

High

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":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
  // REGION SnowConvert Helpers Code
  var RAISE = function (code,name,message) {
    message === undefined && ([name,message] = [message,name])
    var error = new Error(message);
    error.name = name
    SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
    throw error;
  };
  // END REGION

  let TOTAL_COUNT;
  /* ** SSC-EWI-OR0057 - TRANSFORMATION FOR NESTED PROCEDURE OR FUNCTION IS NOT SUPPORTED ** */
  /*    -- Function Declaration
     FUNCTION function_declaration(param1 VARCHAR) RETURN INTEGER; */
  // Function Declaration
  ;
  /* ** 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.

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