User defined function was transformed to a Snowflake procedure.
Severity
Low
Description
Snowflake user defined functions do not support the same features as Oracle or SQL Server. To maintain the functional equivalence the function is transformed to a Snowflake stored procedure. This will affect their usage in queries.
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE PURCHASING.FOO ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTEASCALLERAS$$DECLARE I INT :=0; P INT;BEGINSelectCOUNT(*)INTO :PFROM PURCHASING.VENDOR;WHILE (:P <1000) LOOP I := :I +1; P := :P + :I;ENDLOOP;IF ((:I =6)) THENRETURN1;ENDIF;RETURN :P;END;$$;
Oracle:
IN -> Oracle_01.sql
CREATE OR REPLACEFUNCTIONFUN1(PAR1 VARCHAR)RETURNVARCHARIS VAR1 VARCHAR(20); VAR2 VARCHAR(20);BEGINSELECT COL1 INTO VAR1 FROM TABLE1 where col1 =1; VAR2 := PAR1 || VAR1;RETURN VAR2;END;/
OUT -> Oracle_01.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE FUN1(PAR1 VARCHAR)RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$DECLARE VAR1 VARCHAR(20); VAR2 VARCHAR(20);BEGINSELECT COL1 INTO :VAR1FROM TABLE1where col1 =1; VAR2 := NVL(:PAR1 :: STRING, '') || NVL(:VAR1 :: STRING, '');RETURN :VAR2;END;$$;
Recommendations
Separate the inside queries to maintain the same logic.
The source code may need to be restructured to fit with the Snowflake user-defined functions approach.