Oracle Create Procedure to Snowflake Snow Scripting
Description
Some parts in the output code are omitted for clarity reasons.
A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value. Oracle SQL Language Reference Create Procedure.
For more information regarding Oracle Create Procedure, check here.
Snowflake does not allow output parameters in procedures, a way to simulate this behavior could be to declare a variable and return its value at the end of the procedure.
Parameters with default values
Snowflake does not allow setting default values for parameters in procedures, a way to simulate this behavior could be to declare a variable with the default value or overload the procedure.
3. Procedure with Additional Settings
IN -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE proc3DEFAULT COLLATION USING_NLS_COMPAUTHID CURRENT_USERASBEGINNULL;END;
CREATE OR REPLACE PROCEDURE proc4( param1 NUMBER)IS localVar1 NUMBER; countRows NUMBER; tempSql VARCHAR(100); tempResult NUMBER;CURSOR MyCursor ISSELECT COL1 FROM Table1;BEGIN localVar1 := param1; countRows :=0; tempSql :='SELECT COUNT(*) FROM Table1 WHERE COL1 ='|| localVar1;FOR myCursorItem IN MyCursorLOOP localVar1 := myCursorItem.Col1; countRows := countRows +1; END LOOP;INSERTINTO Table2 VALUES(countRows, 'ForCursor: Total Row count is: '|| countRows); countRows :=0;OPEN MyCursor;LOOPFETCH MyCursor INTO tempResult;EXITWHEN MyCursor%NOTFOUND; countRows := countRows +1;END LOOP;CLOSE MyCursor;INSERTINTO Table2 VALUES(countRows, 'LOOP: Total Row count is: '|| countRows);EXECUTE IMMEDIATE tempSql INTO tempResult;IF tempResult >0THENINSERTINTO Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:'|| localVar1 ||' in Table1 -- There are '|| tempResult ||' rows');COMMIT;END IF;END proc3;
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE proc4(param1 NUMBER(38, 18))RETURNS VARCHARLANGUAGE SQLCOMMENT='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$DECLARE localVar1 NUMBER(38, 18); countRows NUMBER(38, 18); tempSql VARCHAR(100); tempResult NUMBER(38, 18); MyCursor CURSORFORSELECT COL1 FROM Table1;BEGIN localVar1 := :param1; countRows :=0; tempSql :='SELECT COUNT(*) FROM Table1WHERE COL1 ='||NVL(:localVar1 :: STRING, '');OPEN MyCursor;--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **FOR myCursorItem IN MyCursor DO localVar1 := myCursorItem.Col1; countRows := :countRows +1;ENDFOR;CLOSE MyCursor;INSERTINTO Table2VALUES(:countRows, 'ForCursor: Total Row count is: '||NVL(:countRows :: STRING, '')); countRows :=0;OPEN MyCursor;LOOP--** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **FETCH MyCursor INTO :tempResult;IF (tempResult IS NULL) THENEXIT;END IF; countRows := :countRows +1;END LOOP;CLOSE MyCursor;INSERTINTO Table2SELECT :countRows,'LOOP: Total Row count is: '||NVL(:countRows :: STRING, '');!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!EXECUTE IMMEDIATE :tempSql !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!INTO tempResult;IF (:tempResult >0) THENINSERTINTO Table2(COL1, COL2)SELECT :tempResult,'Hi, found value:'||NVL(:localVar1 :: STRING, '') ||' in Table1 -- There are '||NVL(:tempResult :: STRING, '') ||' rows';--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **COMMIT;END IF;END;$$;
5. Procedure with empty RETURN statements
In Oracle procedures you can have empty RETURN statements to finish the execution of a procedure. In Snowflake Scripting procedures can have RETURN statements but they must have a value. By default all empty RETURN statements are converted with a NULL value.
IN -> Oracle_05.sql
-- Procedure with empty returnCREATEORREPLACEPROCEDURE MY_PROCISBEGINNULL;RETURN;END;
OUT -> Oracle_05.sql
-- Procedure with empty returnCREATEORREPLACEPROCEDURE MY_PROC ()RETURNSVARCHARLANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$BEGINNULL;RETURNNULL;END;$$;
RETURN statements in procedures with output parameters
In procedures with output parameters, instead of a NULL value an OBJECT_CONSTRUCT will be used in the empty RETURN statements to simulate the output parameters in Snowflake Scripting.