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.

Oracle Create Procedure Syntax

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.

Snowflake Create Procedure Syntax

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

Oracle

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;

Snow Scripting

CREATE OR REPLACE PROCEDURE proc1()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
NULL;
END;
$$;

2. Procedure with Different Parameters

Oracle

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;

Snow Scripting

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

Oracle

CREATE OR REPLACE PROCEDURE proc3
DEFAULT COLLATION USING_NLS_COMP
AUTHID CURRENT_USER
AS
BEGIN
NULL;
END;

Snow Scripting

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

Oracle

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;

Snow Scripting

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

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.

Oracle

-- Procedure with empty return
CREATE OR REPLACE PROCEDURE MY_PROC
IS
BEGIN
   NULL;
   RETURN;
END;

Snowflake Scripting

-- 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

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.

Oracle

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;

Snowflake Scripting

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

1. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.

2. MSCEWI3097: Procedure Properties are Not Supported in Snowflake Procedures.