CREATE PROCEDURE
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.
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
PROCEDURE
[ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ] [ sharing_clause ]
[ ( default_collation_option | invoker_rights_clause | accessible_by_clause)... ]
{ IS | AS } { [ declare_section ]
BEGIN statement ...
[ EXCEPTION exception_handler [ exception_handler ]... ]
END [ name ] ;
|
{ java_declaration | c_declaration } } ;
For more information regarding Snowflake Create Procedure, check here.
CREATE [ OR REPLACE ] PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS <result_data_type> [ NOT NULL ]
LANGUAGE SQL
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
[ EXECUTE AS { CALLER | OWNER } ]
AS '<procedure_definition>'
Sample Source Patterns
1. Basic Procedure
CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;
CREATE OR REPLACE PROCEDURE proc1()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
NULL;
END;
$$;
2. Procedure with Different Parameters
CREATE OR REPLACE PROCEDURE proc2
(
p1 OUT INTEGER,
p2 OUT INTEGER,
p3 INTEGER := 1,
p4 INTEGER DEFAULT 1
) AS
BEGIN
p1 := 17;
p2 := 93;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.proc2
(
p1 NUMBER,
p2 NUMBER,
p3 NUMBER,
p4 NUMBER
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
p1 := 17;
p2 := 93;
END;
$$;
Output parameters
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 proc3
DEFAULT COLLATION USING_NLS_COMP
AUTHID CURRENT_USER
AS
BEGIN
NULL;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.proc3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
/*** MSC-WARNING - MSCEWI3097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/
AS
$$
BEGIN
NULL;
END;
$$
4. Procedure with Basic Statements
CREATE OR REPLACE PROCEDURE proc4
(
param1 NUMBER
)
IS
localVar1 NUMBER;
countRows NUMBER;
tempSql VARCHAR(100);
tempResult NUMBER;
CURSOR MyCursor
IS
SELECT COL1 FROM Table1;
BEGIN
localVar1 := param1;
countRows := 0;
tempSql := 'SELECT COUNT(*) FROM Table1 WHERE COL1 =' || localVar1;
FOR myCursorItem IN MyCursor
LOOP
localVar1 := myCursorItem.Col1;
countRows := countRows + 1;
END LOOP;
INSERT INTO Table2 VALUES(countRows, 'ForCursor: Total Row count is: ' || countRows);
countRows := 0;
OPEN MyCursor;
LOOP
FETCH MyCursor INTO tempResult;
EXIT WHEN MyCursor%NOTFOUND;
countRows := countRows + 1;
END LOOP;
CLOSE MyCursor;
INSERT INTO Table2 VALUES(countRows, 'LOOP: Total Row count is: ' || countRows);
EXECUTE IMMEDIATE tempSql INTO tempResult;
IF tempResult > 0 THEN
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 VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
localVar1 NUMBER;
countRows NUMBER;
tempSql STRING;
tempResult NUMBER;
MyCursor CURSOR FOR
SELECT 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;
END FOR;
INSERT INTO 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;
INSERT INTO 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
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.
-- Procedure with empty return
CREATE OR REPLACE PROCEDURE MY_PROC
IS
BEGIN
NULL;
RETURN;
END;
-- Procedure with empty returns
CREATE OR REPLACE PROCEDURE PUBLIC.MY_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
NULL;
RETURN NULL;
END;
$$;
RETURN
statements in procedures with output parameters
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.
CREATE OR REPLACE PROCEDURE PROC_WITH_OUTPUT_PARAMETERS
(param1 OUT NUMBER, param2 OUT NUMBER, param3 NUMBER)
IS
BEGIN
IF param3 > 0 THEN
param1 := 2;
param2 := 1000;
RETURN;
END IF;
param1 := 5;
param2 := 3000;
END;
CREATE OR REPLACE PROCEDURE 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 VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
IF (: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);
END IF;
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.
Last updated
Was this helpful?