DEFAULT PARAMETERS

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

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);0016

Snowflake

Default parameter declaration

Oracle

Query

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1 (param1 NUMBER(38, 18),
   param2 NUMBER(38, 18) DEFAULT TO_NUMBER(1)
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS2 (
   param1 NUMBER(38, 18) DEFAULT 1,
   param2 NUMBER(38, 18) DEFAULT 2
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE1(COL1, COL2)
		   VALUES(:param1, :param2);
	END;
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_DEAFAULT_PARAMS3 (
   param1 NUMBER(38, 18) DEFAULT 100, param2 NUMBER(38, 18),
   param3 NUMBER(38, 18) DEFAULT 1000
)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	BEGIN
		INSERT INTO TABLE2(COL1, COL2, COL3)
		   VALUES (:param1, :param2, :param3);
	END;
$$;

Calling procedures with default parameters

Oracle

Query

IN -> Oracle_02.sql

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

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        PROC_WITH_DEFAULT_PARAMS1(10, 15);
        CALL
        PROC_WITH_DEFAULT_PARAMS1(10);
        CALL
        PROC_WITH_DEFAULT_PARAMS2(10, 15);
        CALL
        PROC_WITH_DEFAULT_PARAMS2(10);
        CALL
        PROC_WITH_DEFAULT_PARAMS2();
    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

IN -> Oracle_03.sql

CALL PROC_WITH_DEFAULT_CALLS();

SELECT * FROM TABLE1;

Result

Snowflake Scripting

Query

OUT -> Oracle_03.sql

CALL PROC_WITH_DEFAULT_CALLS();

SELECT * FROM TABLE1;

Result

Calling procedures with named arguments and default parameters

Oracle

Query

IN -> Oracle_04.sql

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

OUT -> Oracle_04.sql

CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_CALLS2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 20, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10, 30);
        CALL
        PROCEDURE_WITH_DEAFAULT_PARAMS3(10);
    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

IN -> Oracle_05.sql

CALL PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM TABLE2;

Result

Snowflake Scripting

Query

OUT -> Oracle_05.sql

CALL PROC_WITH_DEFAULT_CALLS2();

SELECT * FROM TABLE2;

Result

Known Issues

1. No issues found

Related EWIs

No related EWIs.