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. .
For more information regarding Oracle Create Procedure, check .
For more information regarding Snowflake Create Procedure, check .
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
IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
null;
END;
$$;
2. Procedure with Different Parameters
IN -> Oracle_02.sql
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;
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE proc2
(p1 INTEGER, p2 INTEGER,
p3 INTEGER DEFAULT 1,
p4 INTEGER DEFAULT 1
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
p1 := 17;
p2 := 93;
RETURN OBJECT_CONSTRUCT('p1', :p1, 'p2', :p2);
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
IN -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE proc3
DEFAULT COLLATION USING_NLS_COMP
AUTHID CURRENT_USER
AS
BEGIN
NULL;
END;
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE proc3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"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
$$
BEGIN
NULL;
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 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;
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE proc4
(param1 NUMBER(38, 18)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
localVar1 NUMBER(38, 18);
countRows NUMBER(38, 18);
tempSql VARCHAR(100);
tempResult NUMBER(38, 18);
MyCursor CURSOR
FOR
SELECT
OBJECT_CONSTRUCT('COL1', COL1) sc_cursor_record FROM
Table1;
BEGIN
localVar1 := :param1;
countRows := 0;
tempSql := 'SELECT COUNT(*) FROM
Table1
WHERE COL1 =' || NVL(:localVar1 :: STRING, '');
OPEN MyCursor;
--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
FOR myCursorItem IN MyCursor DO
LET myCursorItem OBJECT := myCursorItem.sc_cursor_record;
localVar1 := myCursorItem.Col1;
countRows := :countRows + 1;
END FOR;
CLOSE MyCursor;
INSERT INTO Table2
VALUES(: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) THEN
EXIT;
END IF;
countRows := :countRows + 1;
END LOOP;
CLOSE MyCursor;
INSERT INTO Table2
SELECT
:countRows,
'LOOP: Total Row count is: ' || NVL(:countRows :: STRING, '');
EXECUTE IMMEDIATE :tempSql
-- --** SSC-FDM-0024 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- INTO tempResult
;
IF (:tempResult > 0) THEN
INSERT INTO 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 return
CREATE OR REPLACE PROCEDURE MY_PROC
IS
BEGIN
NULL;
RETURN;
END;
OUT -> Oracle_05.sql
-- Procedure with empty return
CREATE OR REPLACE PROCEDURE MY_PROC ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
NULL;
RETURN NULL;
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.
IN -> Oracle_06.sql
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;
OUT -> Oracle_06.sql
CREATE OR REPLACE PROCEDURE PROC_WITH_OUTPUT_PARAMETERS (param1 NUMBER(38, 18), param2 NUMBER(38, 18), param3 NUMBER(38, 18)
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
BEGIN
IF (:param3 > 0) THEN
param1 := 2;
param2 := 1000;
RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);
END IF;
param1 := 5;
param2 := 3000;
RETURN OBJECT_CONSTRUCT('param1', :param1, 'param2', :param2);
END;
$$;
6. Procedure with DEFAULT parameters
DEFAULT parameters allow named parameters to be initialized with default values if no value is passed.
IN -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE TEST(
X IN VARCHAR DEFAULT 'P',
Y IN VARCHAR DEFAULT 'Q'
)
AS
varX VARCHAR(32767) := NVL(X, 'P');
varY NUMBER := NVL(Y, 1);
BEGIN
NULL;
END TEST;
BEGIN
TEST(Y => 'Y');
END;
OUT -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE TEST (
X VARCHAR DEFAULT 'P',
Y VARCHAR DEFAULT 'Q'
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
varX VARCHAR(32767) := NVL(:X, 'P');
varY NUMBER(38, 18) := NVL(:Y, 1 :: STRING);
BEGIN
NULL;
END;
$$;
DECLARE
call_results VARIANT;
BEGIN
CALL
TEST(Y => 'Y');
RETURN call_results;
END;
Known Issues
1. Unsupported OUT parameters
Snowflake procedures do not have a native option for output parameters.
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
: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.
: This statement has usages of cursor for loop.
Functionality is not currently supported by Snowflake Scripting.
: COMMIT and ROLLBACK statements require adequate setup to perform as intended.
