Packages

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

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

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)

CREATE SCHEMA IF NOT EXISTS MY_PACKAGE;

/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*  PROCEDURE MY_PROCEDURE(PARAM1 VARCHAR);*/;

/*** MSC-WARNING - MSCEWI3034 - PACKAGE FUNCTION DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*  FUNCTION MY_FUNCTION(PARAM1 VARCHAR) RETURN NUMBER (38,19) ;*/;

Transformation with option 2

/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*  PROCEDURE MY_PROCEDURE(PARAM1 VARCHAR);*/;

/*** MSC-WARNING - MSCEWI3034 - PACKAGE FUNCTION DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*  FUNCTION MY_FUNCTION(PARAM1 VARCHAR) RETURN NUMBER (38,19) ;*/;

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

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

CREATE SCHEMA IF NOT EXISTS PACKAGE_VARIABLES;

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

SET "PACKAGE_VARIABLES.VAR2" = (SELECT '#' || (SUBSTR(GETVARIABLE('PACKAGE_VARIABLES.VAR1'), 2) :: 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

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

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

CREATE SCHEMA IF NOT EXISTS PACKAGE_CONSTANTS;
/*** MSC-WARNING - MSCEWI3054 - PACKAGE CONSTANTS IN STATEFUL PACKAGE ARE DECLARED INSIDE THE PROCEDURE OR FUNCTIONS THAT USE THEM ***/
  /*const_name CONSTANT VARCHAR(10) := 'Snow';*/;
  
/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***/
  /*PROCEDURE PROCEDURE1;*/;

CREATE OR REPLACE PROCEDURE PACKAGE_CONSTANTS.MY_PROCEDURE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   const CONST_NAME = `Snow`;
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`INSERT INTO PUBLIC.DBUSER (USER_NAME)
      VALUES (?)`,[CONST_NAME]);
$$;

Other Package members

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

Oracle

CREATE OR REPLACE PACKAGE MY_PACKAGE_EX AS
    an_exception EXCEPTION;
END MY_PACKAGE_EX;

Transformation with option 1

CREATE SCHEMA IF NOT EXISTS MY_PACKAGE_EX;

/*** MSC-ERROR - MSCEWI3049 - 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

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

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

CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE.MY_PROCEDURE (MY_PARAM STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   null;
$$;

Transformation with option 2

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

CREATE OR REPLACE PROCEDURE PACKAGE_PROCEDURE_MY_PROCEDURE (MY_PARAM STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   null;
$$;

Function Definition

Functions inside package bodies are converted into Snowflake stored procedures.

Oracle

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

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
AS
$$
   // REGION SnowConvert Helpers Code
   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
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

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.

CREATE OR REPLACE PROCEDURE PUBLIC.PROCEDURE02 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   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
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

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

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

CREATE OR REPLACE PROCEDURE PACKAGE_VARIABLES.P1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   try {
      PACKAGE_VARIABLES.VAR1 = PACKAGE_VARIABLES.VAR1 + 888;
      EXEC(`INSERT INTO PUBLIC.TABLE1 values (?)`,[PACKAGE_VARIABLES.VAR1]);
      EXEC(`INSERT INTO PUBLIC.TABLE2 values (?)`,[PACKAGE_VARIABLES.VAR4]);
   } finally {
      PACKAGE_VARIABLES.saveState()
   }
$$;
PreviousUser defined functionsNextHelpers

Last updated