DEFAULT PARAMETERS

This article is about the current transformation of the default parameters and how their functionality is being emulated.

Description

A default parameter is a parameter that has a value in case an argument is not passed in the procedure or function call. Since Snowflake doesn't support default parameters, SnowConvert inserts the default value in the procedure or function call.

In the declaration, the DEFAULT VALUE clause of the parameter is removed. Both syntaxes, the := symbol and the DEFAULT clause, are supported.

Sample Source Patterns

Sample auxiliary code

Oracle

CREATE TABLE TABLE1(COL1 NUMBER, COL2 NUMBER);
CREATE TABLE TABLE2(COL1 NUMBER, COL2 NUMBER, COL2 NUMBER);

Snowflake

CREATE OR REPLACE TABLE PUBLIC.TABLE1 (
	COL1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
	COL2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
);

CREATE OR REPLACE TABLE PUBLIC.TABLE2 (
	COL1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
	COL2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
	COL3 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
);

Default parameter declaration

Oracle

Query

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (param1 NUMBER, param2 NUMBER default TO_NUMBER(1))
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2) VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (param1 NUMBER default 1, param2 NUMBER default 2)
AS
BEGIN 
	INSERT INTO TABLE1 (COL1, COL2) VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (param1 NUMBER DEFAULT 100, param2 NUMBER, param3 NUMBER DEFAULT 1000)
IS
BEGIN
	INSERT INTO TABLE2(COL1, COL2, COL3) VALUES (param1, param2, param3);
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_WITH_DEFAULT_PARAMS1 (param1 NUMBER (38,18), param2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1089 - The default value TO_NUMBER(1) is not supported by Snowflake. ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO PUBLIC.TABLE1(COL1, COL2) VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_WITH_DEFAULT_PARAMS2 (param1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1089 - The default value 1 is not supported by Snowflake. ***/, param2 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1089 - The default value 2 is not supported by Snowflake. ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO PUBLIC.TABLE1(COL1, COL2) VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3 (param1 NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1089 - The default value 100 is not supported by Snowflake. ***/, param2 NUMBER(38, 18), param3 NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1089 - The default value 1000 is not supported by Snowflake. ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	BEGIN
        	INSERT INTO PUBLIC.TABLE2(COL1, COL2, COL3) VALUES (:param1, :param2, :param3);
	END;
$$;

Calling procedures with default parameters

Oracle

Query

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS
AS
BEGIN 
	PROC_WITH_DEFAULT_PARAMS1(10, 15);
	PROC_WITH_DEFAULT_PARAMS1(10);
	PROC_WITH_DEFAULT_PARAMS2(10, 15);
	PROC_WITH_DEFAULT_PARAMS2(10);
	PROC_WITH_DEFAULT_PARAMS2();
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_WITH_DEFAULT_CALLS ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
        BEGIN
		CALL PUBLIC.PROC_WITH_DEFAULT_PARAMS1(10, 15);
		CALL PUBLIC.PROC_WITH_DEFAULT_PARAMS1(10, TO_NUMBER(1) /*** MSC-WARNING - MSCEWI1090 - Default value added ***/);
		CALL PUBLIC.PROC_WITH_DEFAULT_PARAMS2(10, 15);
		CALL PUBLIC.PROC_WITH_DEFAULT_PARAMS2(10, 2 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/);
		CALL PUBLIC.PROC_WITH_DEFAULT_PARAMS2(1 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/, 2 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/);
	END;
$$;

In order to check that the functionality is being emulated correctly the following query is going to execute the procedure and a SELECT from the table mentioned before.

Oracle

Query

CALL PROC_WITH_DEFAULT_CALLS();

SELECT * FROM TABLE1;

Result

|COL1|COL2|
|----|----|
|10  |15  |
|10  |1   |
|10  |15  |
|10  |2   |
|1   |2   |

Snowflake Scripting

Query

CALL  PUBLIC.PROC_WITH_DEFAULT_CALLS();

SELECT * FROM PUBLIC.TABLE1;

Result

|COL1|COL2|
|----|----|
|10  |15  |
|10  |1   |
|10  |15  |
|10  |2   |
|1   |2   |

Calling procedures with named arguments and default parameters

Oracle

Query

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS2
AS
BEGIN 
    PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param1 => 10, param2 => 20, param3 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param3 => 10, param1 => 20, param2 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param3 => 10, param2 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param2 => 10, param3 => 30);
    PROCEDURE_WITH_DEAFAULT_PARAMS3(param2 => 10);
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.PROC_WITH_DEFAULT_CALLS2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
	BEGIN
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
                /*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
                /*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(20, 30, 10);
                /*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(100 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/, 30, 10);
                /*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(100 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/, 10, 30);
                /*** MSC-WARNING - MSCEWI1079 - PARAMETER NAMES WERE REMOVED FROM CALL, CHECK PARAMETER ORDER ***/
                CALL PUBLIC.PROCEDURE_WITH_DEAFAULT_PARAMS3(100 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/, 10, 1000 /*** MSC-WARNING - MSCEWI1090 - Default value added ***/);
	END;
$$;

In order to check that the functionality is being emulated correctly the following query is going to execute the procedure and a SELECT from the table mentioned before.

Oracle

Query

CALL PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM TABLE2;

Result

|COL1|COL2|COL3|
|----|----|----|
|10  |20  |30  |
|10  |20  |30  |
|20  |30  |10  |
|100 |30  |10  |
|100 |10  |30  |
|100 |10  |1000|

Snowflake Scripting

Query

CALL  PUBLIC.PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM PUBLIC.TABLE12;

Result

|COL1|COL2|COL3|
|----|----|----|
|10  |20  |30  |
|10  |20  |30  |
|20  |30  |10  |
|100 |30  |10  |
|100 |10  |30  |
|100 |10  |1000|

Known Issues

1. No issues found

Related EWIs

1. MSCEWI1079: Parameter names were removed from the call.

2. MSCEWI1087: Arguments do not match the called procedure parameters count, default parameters are not supported yet.

3. MSCEWI1089: Default Value Removed.

4. MSCEWI1090: Default value added.