Constants

Translation spec for Package Constants

Description

PACKAGE CONSTANTS can be declared either in the package declaration or in the PACKAGE BODY. When a package constant is used in a procedure, a new variable is declared with the same name and value as the constant, so the resulting code is pretty similar to the input.

constant CONSTANT datatype [NOT NULL] { := | DEFAULT } expression ;

Sample Source Patterns

Sample auxiliary code

IN -> Oracle_01.sql
create table table1(id number);

Oracle

IN -> Oracle_02.sql
CREATE OR REPLACE PACKAGE PKG1 AS
    PROCEDURE procedure1;
    package_constant CONSTANT NUMBER:= 9999;
END PKG1;

CREATE OR REPLACE PACKAGE BODY PKG1 AS
    PROCEDURE procedure1 AS
    BEGIN
        INSERT INTO TABLE1(ID) VALUES(package_constant);
    END;
END PKG1;

CALL PKG1.procedure1();

SELECT * FROM TABLE1;

Snowflake

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

CREATE OR REPLACE PROCEDURE PKG1.procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        PACKAGE_CONSTANT NUMBER := 9999;
    BEGIN
        INSERT INTO TABLE1(ID) VALUES(:PACKAGE_CONSTANT);
    END;
$$;

CALL PKG1.procedure1();

SELECT * FROM
    TABLE1;

Note that thePROCEDURE definition is being removed since it is not required in Snowflake.

Known Issues

No issues were found.

Last updated