Packages

This is a translation reference to convert Oracle Packages to Snowflake.

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

Package Declaration

This section shows the equivalence between Oracle Package Declaration members and Snowflake statements.

Package Translation options

There are two options to migrate packages, each option will affect directly the naming of the objects inside the package. Check here how you can change this mode in the UI, or check here to change it using the Command Line Interface.

Let's suppose that we have the next scenario in Oracle:

  • A package named MY_PACKAGE.

  • A procedure inside the package named MY_PROCEDURE.

Option 1 (Using new schema)

With this option, packages are transformed into new schemas. Package elements like functions and procedures are created inside the new schema. If the package is already inside a schema, the name of the package will be joined with the name of the schema with an underscore.

This is the default option for translating packages.

Result:

  • An schema will be created with the name MY_PACKAGE.

  • Qualified name of the procedure will be updated to MY_PACKAGE.MY_PROCEDURE.

  • It the package is inside an schema then the procedure will be updated to MY_SCHEMA_MY_PACKAGE.MY_PROCEDURE.

Option 2

With this option, the name of the package elements will be joined with the package name with an underscore. New schemas will not be created.

Result:

  • Name of the procedure will be updated to MY_PACKAGE_MY_PROCEDURE.

  • It the package is inside an schema then the procedure will be updated to MY_SCHEMA.MY_PACKAGE_MY_PROCEDURE.

Create Package

The CREATE PACKAGE statement will be converted to a CREATE SCHEMA statement. Any member inside the package will be converted outside of the package.

Oracle

CREATE OR REPLACE PACKAGE MY_PACKAGE AS
-- Other elements...  
END MY_PACKAGE ;

Transformation with option 1 (Using new schema)

CREATE IF NOT EXISTS SCHEMA MY_PACKAGE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
-- Other elements...

Transformation with option 2

With this option, the Schema won't be generated and only the inner elements will be kept but with their names renamed.

-- Other elements...

Procedure and function declaration

Procedure and function declarations are not necessary for the transformation to Snowflake. Existing procedure or function declarations will be commented out.

Oracle

IN -> Oracle_01.sql
CREATE OR REPLACE PACKAGE MY_PACKAGE AS
  PROCEDURE MY_PROCEDURE(PARAM1 VARCHAR2);
  FUNCTION MY_FUNCTION(PARAM1 VARCHAR2) RETURN NUMBER ;
END MY_PACKAGE;

Transformation with option 1 (Using new schema)

OUT -> Oracle_01.sql
CREATE SCHEMA IF NOT EXISTS MY_PACKAGE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

Note that that for option 1, the PROCEDURE definition in the package is removed since it is not required by Snowflake.

Variables declaration

You might also be interested in variables helper.

Oracle package variables are transformed into Snowflake Session Variables. A prefix is added to the values to know what type it is inside stored procedures. If the value should be null, a "~" is added. Because of this, variables that depend on other variables will require a SUBSTR and a CAST.

Data type and Code mappings

Data type or value

Code

Numeric types

#

Datetime types

&

String types

$

NULL values

~

The transformation of the variables will be always the same regardless of the transformation option.

Oracle

IN -> Oracle_02.sql
CREATE OR REPLACE PACKAGE PACKAGE_VARIABLES AS 
    VAR1 integer := 333;
    VAR2 INTEGER := VAR1 + 456;
	  VAR3 DATE := CURRENT_DATE;
	  VAR4 VARCHAR(20) := 'HELLO WORLD';
	  VAR5 INTEGER;
END;

Snowflake

OUT -> Oracle_02.sql
CREATE SCHEMA IF NOT EXISTS PACKAGE_VARIABLES
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

SET "PACKAGE_VARIABLES.VAR1" = '' || (333);

SET "PACKAGE_VARIABLES.VAR2" = (SELECT
	'' || (GETVARIABLE('PACKAGE_VARIABLES.VAR1') :: INTEGER + 456));

SET "PACKAGE_VARIABLES.VAR3" = (SELECT
	'' || (CURRENT_DATE()));

SET "PACKAGE_VARIABLES.VAR4" = '' || ('HELLO WORLD');

SET "PACKAGE_VARIABLES.VAR5" = '~';

Constants declaration

Constants declaration will be declared inside the procedure or functions that use them. Existing package constants declaration will be commented out and a warning will be added.

Oracle

IN -> Oracle_03.sql
CREATE OR REPLACE PACKAGE PACKAGE_CONSTANTS
IS
const_name CONSTANT VARCHAR(10) := 'Snow';
PROCEDURE PROCEDURE1;
END PACKAGE_CONSTANTS;

CREATE OR REPLACE PACKAGE BODY PACKAGE_CONSTANTS
IS
PROCEDURE MY_PROCEDURE IS 
   BEGIN 
      INSERT INTO DBUSER ("USER_NAME") 
      VALUES (const_name);
   END;

END PACKAGE_CONSTANTS;

Transformation with option 1

OUT -> Oracle_03.sql
CREATE SCHEMA IF NOT EXISTS PACKAGE_CONSTANTS
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE PACKAGE_CONSTANTS.MY_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      CONST_NAME VARCHAR(10) := 'Snow';
   BEGIN
      INSERT INTO DBUSER("USER_NAME")
      VALUES (:CONST_NAME);
   END;
$$;

Note that the PROCEDURE definition in the package is removed since it is not required by Snowflake.

Other Package members

The transformation for other package members like cursors, exceptions and user defined types, is still a work in progress.

Oracle

IN -> Oracle_04.sql
CREATE OR REPLACE PACKAGE MY_PACKAGE_EX AS
    an_exception EXCEPTION;
END MY_PACKAGE_EX;

Transformation with option 1

OUT -> Oracle_04.sql
CREATE SCHEMA IF NOT EXISTS MY_PACKAGE_EX
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

!!!RESOLVE EWI!!! /*** SSC-EWI-OR0049 - PACKAGE EXCEPTIONS in stateful package MY_PACKAGE_EX are not supported yet ***/!!!
an_exception EXCEPTION;

Transformation with option 2

/*** MSC-ERROR - MSCEWI3049 - PACKAGE EXCEPTIONS in stateful package MY_PACKAGE_EX are not supported yet ***/
  /*an_exception EXCEPTION;*/;

Package Body Definition

This section shows the equivalence between Oracle Package Body Definition members and Snowflake statements.

Create Package Body

Elements inside a Package Body are going to be extracted from the package. The package body will disappear so the Create Package Body statement is removed in the converted code.

Procedure Definition

Stored Procedures inside packages use the same transformations defined in the PL/SQL Translation Reference.

Oracle

IN -> Oracle_05.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PACKAGE BODY PACKAGE_PROCEDURE
IS
PROCEDURE MY_PROCEDURE (MY_PARAM VARCHAR) IS 
   BEGIN 
      null;
   END;

END PACKAGE_PROCEDURE;

Transformation with option 1

OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE.MY_PROCEDURE (MY_PARAM STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // SnowConvert Helpers Code section is omitted.

   null;
$$;

Transformation with option 2

CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE_MY_PROCEDURE (MY_PARAM STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   null;
$$;

Function Definition

Functions inside package bodies are converted into Snowflake stored procedures.

Oracle

IN -> Oracle_06.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PACKAGE BODY PACKAGE_FUNCTION
IS
FUNCTION MY_FUNCTION (MY_PARAM VARCHAR) RETURN NUMBER 
AS
   BEGIN 
      null;
   END;
END PACKAGE_FUNCTION;

Transformation with option 1

OUT -> Oracle_06.sql
CREATE OR REPLACE FUNCTION PACKAGE_FUNCTION.MY_FUNCTION (MY_PARAM STRING)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
   // SnowConvert Helpers Code section is omitted.

   null;
$$;

Transformation with option 2

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE FUNCTION PACKAGE_FUNCTION_MY_FUNCTION (MY_PARAM STRING)
RETURNS NUMBER
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
   // REGION SnowConvert Helpers Code
   null;
$$;

Other package body members

Please refer to the "other package members" section in Package declaration.

Using package members

Call of procedures inside packages

If the procedure is inside a package and the package is inside a schema, the call will be renamed.

Oracle

IN -> Oracle_07.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
    SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);
END;

CALL SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);

Transformation with option 1

SnowConvert helpers Code removed from the example. You can find them here.

OUT -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE PROCEDURE02 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    EXEC(`CALL
SCHEMA1.PACKAGE1.PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

CALL SCHEMA1.PACKAGE1.PROCEDURE01(param1, param2);

Transformation with option 2

SnowConvert helpers Code removed from the example. You can find them here.

With this option, the call of the procedures will be renamed accordingly to the rename of the procedure declaration. The schema name will be separated from the procedure name with a dot.

Snowflake

CREATE OR REPLACE PROCEDURE PUBLIC.PROCEDURE02 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   EXEC(`CALL SCHEMA1.PACKAGE1_PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

CALL SCHEMA1.PACKAGE1_PROCEDURE01(param1, param2);

Package variables inside procedures

Packages variables are transformed to session variables. Those variables are usable through the "Package variables helper".

This sample is using variables declared in packages Variables declaration section.

Oracle

IN -> Oracle_08.sql
--Additional Params: -t JavaScript

CREATE OR REPLACE PACKAGE BODY PACKAGE_VARIABLES AS
  PROCEDURE P1 AS
    BEGIN         
			VAR1 := VAR1 + 888;         
			INSERT INTO TABLE1 values (VAR1);
         INSERT INTO TABLE2 values (VAR4);
    END;
END;

Snowflake

OUT -> Oracle_08.sql
CREATE OR REPLACE PROCEDURE PACKAGE_VARIABLES.P1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// SnowConvert Helpers Code section is omitted.
	
	VAR1 =
			!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT VAR1 MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
			VAR1 + 888;
	EXEC(`INSERT INTO TABLE1
			values (VAR1)`);
	EXEC(`INSERT INTO TABLE2
         values (VAR4)`);
$$;

Related EWIs

PreviousUser defined functionsNextHelpers

Last updated