Oracle Create Procedure to Snowflake Snow Scripting
Description
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
CREATE OR REPLACE PROCEDURE proc3DEFAULT COLLATION USING_NLS_COMPAUTHID CURRENT_USERASBEGINNULL;END;
CREATE OR REPLACE PROCEDURE PUBLIC.proc3 ()RETURNS VARCHARLANGUAGE SQLEXECUTE AS CALLER/*** MSC-WARNING - MSCEWI3097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/AS$$BEGINNULL;END;$$
4. Procedure with Basic Statements
CREATE OR REPLACE PROCEDURE proc4( param1 NUMBER)IS localVar1 NUMBER; countRows NUMBER; tempSql VARCHAR(100); tempResult NUMBER;CURSOR MyCursorISSELECT 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 >0THEN INSERT INTO Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:' || localVar1 || ' in Table1 -- There are ' || tempResult || ' rows');
COMMIT;END IF;END proc3;
CREATE OR REPLACE PROCEDURE PUBLIC.proc4(param1 FLOAT)RETURNS VARCHARLANGUAGE SQLEXECUTE AS CALLERAS$$DECLARE localVar1 NUMBER; countRows NUMBER; tempSql STRING; tempResult NUMBER; MyCursor CURSORFORSELECT COL1 FROM PUBLIC.Table1;BEGIN localVar1 := :param1; countRows :=0; tempSql :='SELECT COUNT(*) FROM PUBLIC.Table1 WHERE COL1 ='||NVL(:localVar1 :: STRING, '');FOR myCursorItem IN MyCursor DO localVar1 := myCursorItem.Col1; countRows := :countRows +1;ENDFOR;INSERTINTO PUBLIC.Table2 VALUES(:countRows, 'ForCursor: Total Row count is: '||NVL(:countRows :: STRING, '')); countRows :=0;OPEN MyCursor;-- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR LOOP IS PLANNED TO BE DELIVERED IN THE FUTURE **-- LOOP-- FETCH MyCursor INTO tempResult;-- EXIT WHEN MyCursor%NOTFOUND;-- countRows := countRows + 1;-- END LOOP;CLOSE MyCursor;INSERTINTO PUBLIC.Table2 VALUES(:countRows, 'LOOP: Total Row count is: '||NVL(:countRows :: STRING, ''));-- ** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED **
-- EXECUTE IMMEDIATE :tempSql INTO :tempResult ;IF (:tempResult >0) THEN INSERT INTO PUBLIC.Table2 (COL1, COL2) VALUES(:tempResult, 'Hi, found value:' || NVL(:localVar1 :: STRING, '') || ' in Table1 -- There are ' || NVL(:tempResult :: STRING, '') || ' rows');
-- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR COMMIT IS PLANNED TO BE DELIVERED IN THE FUTURE **-- 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.
-- Procedure with empty returnCREATEORREPLACEPROCEDURE MY_PROCISBEGINNULL;RETURN;END;
-- Procedure with empty returnsCREATEORREPLACEPROCEDURE PUBLIC.MY_PROC ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$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.
CREATEORREPLACEPROCEDURE PUBLIC.PROC_WITH_OUTPUT_PARAMETERS(param1 NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/, param2 NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED ***/, param3 NUMBER(38, 18))
RETURNS VARIANTLANGUAGESQLEXECUTEASCALLERAS$$BEGINIF (:param3 >0) THEN param1 :=2; param2 :=1000; --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);ENDIF; param1 :=5; param2 :=3000; /*** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ***/
RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);END;$$;
Known Issues
1. Unsupported OUT and the default value for parameters
Snowflake procedures do not have a native option for output parameters, or to set a default value for them.
2. Unsupported Oracle additional settings
The following Oracle settings and clauses are not supported by Snowflake procedures:
sharing_clause
default_collation_option
invoker_rights_clause
accessible_by_clause
java_declaration
c_declaration
Related EWIS
MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.
MSCEWI3097: Procedure Properties are Not Supported in Snowflake Procedures.