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;
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE proc3 ()RETURNS VARCHARLANGUAGE SQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER!!!RESOLVE EWI!!! /*** SSC-EWI-OR0097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/!!!AS$$BEGINNULL;END;$$;
4. Procedure with Basic Statements
IN -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE proc4( param1 NUMBER)IS localVar1 NUMBER; countRows NUMBER; tempSql VARCHAR(100); tempResult NUMBER;CURSOR MyCursor ISSELECT COL1 FROM Table1;