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

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.