PROCEDURE CALL

Translation reference for PROCEDURE CALL aka SUBPROGRAM INVOCATION

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

Description

This section describes the syntax for subprogram invocations within PL blocks, such as procedures or anonymous blocks.

For more information on this subject, please refer to Oracle's Subprogram documentation: (Oracle PL/SQL Language Reference Subprogram Invocation Statement)

Procedure calls can be migrated to Snowflake as long as there are no optional parameters and their order matches the formal parameters. Please note that Procedure invocations get migrated to a Call statement.

Oracle Subprogram Invocation Syntax

<subprogram invocation> := subprogram_name [ ( [ parameter [, parameter]... ] ) ]

<parameter> := {
  <actual parameter>
  | <formal parameter name> => <actual parameter>
  }

Snowflake Scripting has support for this statement, albeit with some functional differences.

Snow Scripting Subprogram Invocation Syntax

<subprogram invocation> := CALL subprogram_name [ ( [ parameter [, parameter]... ] ) ]

<parameter> := {
  <actual parameter>
  | <formal parameter name> => <actual parameter>
  }

Sample Source Patterns

Consider the next table and procedure for the examples below.

Oracle

IN -> Oracle_01.sql

CREATE TABLE procedure_call_test_table(
    col1 INTEGER
);

-- Simple Called procedure
CREATE OR REPLACE PROCEDURE called_procedure (param1 INTEGER)
AS
BEGIN
    INSERT INTO procedure_call_test_table VALUES (param1);
END;

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE procedure_call_test_table (
        col1 INTEGER
    )
    COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
    ;

    -- Simple Called procedure
CREATE OR REPLACE PROCEDURE called_procedure (param1 INTEGER)
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 procedure_call_test_table
        VALUES (:param1);
    END;
$$;

Simple call

Oracle

Query

IN -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE simple_calling_procedure
AS
BEGIN
    called_procedure(1);
END;

CALL simple_calling_procedure();

SELECT * FROM procedure_call_test_table;

Result

COL1|
----+
   1|

Snowflake Scripting

Query

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE simple_calling_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        called_procedure(1);
    END;
$$;

CALL simple_calling_procedure();

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "procedure_call_test_table" **

SELECT * FROM
    procedure_call_test_table;

Result

COL1|
----+
   1|

Calling a procedure with an optional parameter

This sample contains manual intervention for some functional differences and is used to explain them. For more information on these differences, please check the Known Issues section below.

Oracle

Query

IN -> Oracle_03.sql

-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE proc_optional_parameters (param1 INTEGER, param2 INTEGER := 8, param3 INTEGER)
AS
BEGIN
    INSERT INTO procedure_call_test_table VALUES (param1);
    INSERT INTO procedure_call_test_table VALUES (param2);
    INSERT INTO procedure_call_test_table VALUES (param3);
END;

CREATE OR REPLACE PROCEDURE calling_procedure
AS
BEGIN
    -- positional convention
    proc_optional_parameters(1, 2, 3);
    
    -- named convention
    proc_optional_parameters(param1 => 4, param2 => 5, param3 => 6);
    
    -- named convention, second gets ommited
    proc_optional_parameters(param1 => 7, param3 => 9);
    
    -- named convention, different order
    proc_optional_parameters(param3 => 12, param1 => 10, param2 => 11);
END;

CALL calling_procedure();

SELECT * FROM procedure_call_test_table;

Result

COL1|
----+
   1|
   2|
   3|
   4|
   5|
   6|
   7|
   8|
   9|
  10|
  11|
  12|

Snowflake Scripting

Query

OUT -> Oracle_03.sql

-- Procedure with optional parameters
CREATE OR REPLACE PROCEDURE proc_optional_parameters
                                                     !!!RESOLVE EWI!!! /*** SSC-EWI-0002 - DEFAULT PARAMETERS MAY NEED TO BE REORDERED. SNOWFLAKE ONLY SUPPORTS DEFAULT PARAMETERS AT THE END OF THE PARAMETERS DECLARATIONS ***/!!!
                                                     (param1 INTEGER, param2 INTEGER DEFAULT 8, param3 INTEGER)
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 procedure_call_test_table
        VALUES (:param1);
        INSERT INTO procedure_call_test_table
        VALUES (:param2);
        INSERT INTO procedure_call_test_table
        VALUES (:param3);
    END;
$$;

CREATE OR REPLACE PROCEDURE calling_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        CALL
        -- positional convention
        proc_optional_parameters(1, 2, 3);
        CALL

        -- named convention
        proc_optional_parameters(param1 => 4, param2 => 5, param3 => 6);
        CALL

        -- named convention, second gets ommited
        proc_optional_parameters(param1 => 7, param3 => 9);
        CALL

        -- named convention, different order
        proc_optional_parameters(param1 => 10, param2 => 11, param3 => 12);
    END;
$$;

CALL calling_procedure();


SELECT * FROM
    procedure_call_test_table;

Result

COL1|
----+
   1|
   2|
   3|
   4|
   5|
   6|
   7|
   8|
   9|
  10|
  11|
  12|

Known Issues

1. Calling Subprograms with default values is not supported

Snowflake does not support setting default values for parameters. So these will need to be filled into every call.

2. Named parameters are accepted, but not functionally equivalent

These parameters will not cause any compilation errors when ran in Snowflake; however, calls still place them in a positional manner. For this reason, the order of these parameters needs to be checked. SnowConvert does not support checking nor reordering these parameters.

3. Calling Subprograms with Out Parameters is not supported

Snowflake does not have support for parameter modes, however, a solution is being implemented to emulate their functionality. To get more information about the transformation for output parameters please go to the following article Output Parameters.

Related EWIs

1. SSC-EWI-0002: Default Parameters May Need To Be Reordered.
2. SSC-FDM-0007: Element with missing dependencies.