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.

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

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;