CREATE PROCEDURE

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.

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

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;

Snow Scripting

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

Oracle

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;

Snow Scripting

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

Oracle

IN -> Oracle_03.sql

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

Snow Scripting

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

Oracle

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;

Snow Scripting

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.

Oracle

IN -> Oracle_05.sql

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

Snowflake Scripting

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.

Oracle

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;

Snowflake Scripting

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.

Oracle

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;

Snowflake Scripting

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

1. SSC-PRF-0003: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.
2. SSC-PRF-0004: This statement has usages of cursor for loop.
3. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
4. SSC-FDM-OR0012: COMMIT and ROLLBACK statements require adequate setup to perform as intended.