CREATE PROCEDURE
Oracle Create Procedure to Snowflake Snow Scripting
Last updated
Oracle Create Procedure to Snowflake Snow Scripting
Last updated
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. .
For more information regarding Oracle Create Procedure, check .
For more information regarding Snowflake Create Procedure, check .
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.
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.
RETURN
statementsIn 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.
RETURN
statements in procedures with output parametersIn 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.
DEFAULT parameters allow named parameters to be initialized with default values if no value is passed.
Snowflake procedures do not have a native option for output parameters.
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
: Functionality is not currently supported by Snowflake Scripting
: Procedures properties are not supported in Snowflake procedures.
COMMIT and ROLLBACK statements require adequate setup to perform as intended.
: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.
: This statement has usages of cursor for loop.
: The statement below has usages of dynamic SQL